Efficient DAX for Dynamic FX Rate LookupDate by Drill-Level & Depth Slicer on a 40M-Row Model

Hi Digger ,

Thank you for such detailed explaination about your requirement and sharing the pbix.

I tried re-producing the scenario and here are the DAX measures that you can try-

For LookupDate

LookupDate = 
VAR SelectedDepth = SELECTEDVALUE(CurrencyDepth[Depth])
VAR CurrentDate = MAX('__Calendar'[Date])  -- assumes 'Date' table is used in matrix
VAR DrillLevel =
    SWITCH(
        TRUE(),
        ISINSCOPE('__Calendar'[Date].[Day]), "Day",
        ISINSCOPE('__Calendar'[Date].[Month]), "Month",
        ISINSCOPE(__Calendar[Date].[Year]), "Year",
        "Transaction"
    )
VAR Result =
    SWITCH(
        TRUE(),
        SelectedDepth = "Daily" && DrillLevel = "Transaction", MAX(I03_DKD[I02_OP_DATA.1]),
        SelectedDepth = "Daily" && DrillLevel = "Day", CurrentDate,
        SelectedDepth = "Daily" && DrillLevel = "Month", EOMONTH(CurrentDate, 0),
        SelectedDepth = "Daily" && DrillLevel = "Year", MIN(EOMONTH(DATE(YEAR(CurrentDate), 12, 31), 0), TODAY()),

        SelectedDepth = "MonthEnd", EOMONTH(CurrentDate, 0),
        SelectedDepth = "SelectedEndDate", MIN(EOMONTH(MAX('__Calendar'[Date]), 0), Today())
    )
RETURN Result

For FX Rate-

Get FX Rate = 
VAR LookupDate = [LookupDate]
VAr CurrCode = max(PP_Currency_rate[name_2])
RETURN
IF(
    NOT ISBLANK(LookupDate) && NOT ISBLANK(CurrCode),
    CALCULATE(
        MAX(PP_Currency_rate[amount_2]),
        FILTER(
            PP_Currency_rate,
            PP_Currency_rate[name_2]<>BLANK() &&
            PP_Currency_rate[date] <= LookupDate
        )
    )

)

 Then you can use 3 explicit measures shared earlier by the Superuser  for Daily, MonthEnd and SelectedEndDate.
This will give you the results.

Attached file for reference.
Also, Thank you AmiraBedh for sharing your inputs.
Hope this helps!