I have the following tables TradingKPIs and TradingKPIs_Prices, which are linked to each other via the table TradingKPIs_Dates with the column colValToday
.
The table TradingKPIs_Prices contains average prices [avgPrice]
for each [priceindex]
and [delivYear]
for each day in [pricedate]
.
I have the following data slices for the user to choose from, which are used as filters:
TradingKPIs[delivYear]
and TradingKPIs_Dates[colValToday]
.
I now want to create the average price for a specific [priceindex]
, which requires additional logic. This logic is necessary because TradingKPIs_Prices only has [pricedate]
for Monday to Friday and, from today’s perspective, does not have today’s data because it is always loaded one day later.
The logic should be:
If the selected date in the data slice is not in the TradingKPIs_Prices table, then the last existing day in [pricedate]
should be used. Otherwise, take the selected day as usual and create the average price.
My measure looks like this:
avgHPFCDE_EUR =
VAR SelectedDate =
SELECTEDVALUE(TradingKPIs_Dates[Date])
VAR AvgPrice =
IF(
SELECTEDVALUE(TradingKPIs_Dates[colValToday]) = "Today",
CALCULATE(
MAX(TradingKPIs_Prices[avgPrice]),
FILTER(
ALL(TradingKPIs_Prices),
TradingKPIs_Prices[pricedate] < TODAY() &&
TradingKPIs_Prices[delivYear] = SELECTEDVALUE(TradingKPIs[delivYear]) &&
TradingKPIs_Prices[priceindex] = "HPFC DE"
),
LASTDATE(TradingKPIs_Prices[pricedate])
),
CALCULATE(
MAX(TradingKPIs_Prices[avgPrice]),
TradingKPIs_Prices[pricedate] = SelectedDate &&
TradingKPIs_Prices[delivYear] = SELECTEDVALUE(TradingKPIs[delivYear]) &&
TradingKPIs_Prices[priceindex] = "HPFC DE"
)
)
RETURN
AvgPrice
Unfortunately, when I select TODAY or a weekend day, I always get an empty value.
I have already asked ChatGPT and tried countless suggested solutions with MAX etc., but I am not getting anywhere.
I really hope someone can help me with this.