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

Hello !

Thank you for posting on Fabric community.

Well I tried to follow you logic and I can tell you thay you may not rely on Power BI only to achieve what you want.

Why ?

Simply because Power BI can struggle when:

• your lookup logic depends on dynamic joins with time-based filtering per row.

• you have large row-level grain that doesn’t cache well (40M rows with 3 complex measures = 120M lookups)

• you need repeated access to expensive logic (like MAX FX rate <= date)

What I managed to understand (I did try it on small amount of data), you can create a measure for the dynamic LookupDate to avoid duplicating logic across measures:

Selected Lookup Date = 
VAR Depth = SELECTEDVALUE(CurrencyDepth[Depth])
VAR TodayDate = TODAY()

VAR DrillDate =
    SWITCH(
        TRUE(),
        ISINSCOPE(I03_DKD[I02_OP_DATA.1]), MAX(I03_DKD[I02_OP_DATA.1]),
        ISINSCOPE('Calendar'[Date]), MAX('Calendar'[Date]),
        ISINSCOPE('Calendar'[Month]), EOMONTH(MAX('Calendar'[Date]), 0),
        ISINSCOPE('Calendar'[Year]), MIN(EOMONTH(MAX('Calendar'[Date]), 0), TodayDate),
        BLANK()
    )

VAR MonthEndDate =
    IF(NOT ISBLANK(DrillDate), EOMONTH(DrillDate, 0))

VAR SelectedDate =
    CALCULATE(
        MAX('Calendar'[Date]),
        REMOVEFILTERS('Calendar')
    )

VAR ClampedSelectedDate = 
    IF(SelectedDate > TodayDate, TodayDate, SelectedDate)

RETURN
    SWITCH(
        Depth,
        "Daily", DrillDate,
        "MonthEnd", MonthEndDate,
        "SelectedEndDate", ClampedSelectedDate,
        BLANK()
    )

and you need a reusable pattern to get the latest available FX rate up to the LookupDate, to return the most recent FX rate up to the lookup date for the current currency in context :

Get FX Rate =
VAR LookupDate = [Selected Lookup Date]
VAR CurrCode = SELECTEDVALUE(I03_DKD[N01_ACCT.N01_KODAS_VL])

RETURN
IF(
    NOT ISBLANK(LookupDate) && NOT ISBLANK(CurrCode),
    CALCULATE(
        MAX(PP_Currency_rate[amount_2]),
        FILTER(
            PP_Currency_rate,
            PP_Currency_rate[name_2] = CurrCode &&
            PP_Currency_rate[date] <= LookupDate
        )
    )
)

Then you can define 3 explicit measures :

FX Rate Daily = 
VAR __Depth = SELECTEDVALUE(CurrencyDepth[Depth])
RETURN
IF(__Depth = "Daily", [Get FX Rate])

FX Rate MonthEnd = 
VAR __Depth = SELECTEDVALUE(CurrencyDepth[Depth])
RETURN
IF(__Depth = "MonthEnd", [Get FX Rate])

FX Rate SelectedEndDate = 
VAR __Depth = SELECTEDVALUE(CurrencyDepth[Depth])
RETURN
IF(__Depth = "SelectedEndDate", [Get FX Rate])

What I really want you to understand in your case you may rethink of stepping back and ask if some of this complexity belongs outside of Power BI.