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

Hi Digger ,

The LookupDate  measure is designed to dynamically determine the correct date to use for FX rate lookups based on two factors: Depth slicer selection  and Drill level in the matrix 

It uses ISINSCOPE() to detect the current drill level and selects the appropriate date: At Transaction level,Day level,Month level and  Year level.

Beyond this, there’s no reliable way to solve the problem more generically within DAX. Power BI doesn’t expose drill level context beyond what ISINSCOPE() can detect, and it doesn’t allow for dynamic evaluation of slicer selections across unrelated hierarchies in a more granular way. So this approach is about as flexible and robust as DAX allows for this scenario.

Hope this helps clarify things!

Best regards,
Shruti