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

Digger 

I looked into your file, my observations

1. I guess to reduce the file size you removed a lot of dat from your 'I03_DKD' table, it does not contain 2025 data and for 2024, it has jan feb and march data only. 

2. Also, 2024 records corresponds to currency code EUR but your currency table does not contain this code.

its hard for anyone to guess which year's data is in right shape so that they can write the formula.

3. There is one many to many relationship, as you are concerned about performance you should consider handling this 

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

4. I am not sure why you are using actual date column from fact table in rows and date column from the calendar table into columns section of matrix, these two tables are related and those two are the actual date columns on top of which you created the relationship, I guess you need to remove them from columns section

I looked into your measure and you splitted the logic properly into variables, I would sugget you to fix the issue then debug the logic varaible by variable, 

Connect on LinkedIn

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!