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.