A data analyst is working on a Power BI model that contains sales data. The 'Sales' table has a 'SalesDate' column and a 'SalesAmount' column. The analyst needs to create a measure that calculates the 'Sales Amount for the Last 30 Days' based on the latest date present in the 'SalesDate' column, regardless of any external date filters applied to the report. Which DAX expression correctly achieves this requirement?
- ACALCULATE(SUM(Sales[SalesAmount]), KEEPFILTERS(DATESINPERIOD(Sales[SalesDate], MAX(Sales[SalesDate]), -30, DAY)))
- BCALCULATE(SUM(Sales[SalesAmount]), DATESINPERIOD(Sales[SalesDate], MAX(Sales[SalesDate]), -30, DAY))
- CCALCULATE(SUM(Sales[SalesAmount]), LASTDATE(Sales[SalesDate]) - 30, ALL(Sales[SalesDate]))
- DCALCULATE(SUM(Sales[SalesAmount]), DATESINPERIOD(Sales[SalesDate], CALCULATE(MAX(Sales[SalesDate]), ALL(Sales)), -30, DAY))
Show answer & explanationAnswer & explanation
Correct answer: D. CALCULATE(SUM(Sales[SalesAmount]), DATESINPERIOD(Sales[SalesDate], CALCULATE(MAX(Sales[SalesDate]), ALL(Sales)), -30, DAY))
To calculate the 'Sales Amount for the Last 30 Days' based on the absolute latest date in the entire 'Sales' table, we need to first determine that latest date by removing any existing filters from the 'Sales' table using ALL(Sales) within a CALCULATE statement for MAX(Sales[SalesDate]). Then, DATESINPERIOD uses this un-filtered maximum date as its starting point to define the 30-day period. This ensures the calculation is always relative to the overall latest sales date.
Why the other options are wrong
- A. KEEPFILTERS would preserve any existing external date filters, which contradicts the requirement of ignoring them to find the absolute latest date.
- B. This expression calculates the last 30 days based on the MAX(Sales[SalesDate]) *within the current filter context*. If a slicer filters the dates, MAX will be limited by that slicer, not the overall latest date.
- C. LASTDATE returns a single date, not a table of dates. Subtracting 30 from a date doesn't create a date range for filtering. ALL(Sales[SalesDate]) would remove filters from the date column, but the filtering logic is incorrect.
Time Intelligence with Absolute Latest Date
Calculating measures based on the absolute latest date in the entire dataset, ignoring external date filters, requires using ALL() or ALLEXCEPT() within a CALCULATE to determine the maximum date before applying time intelligence functions.
- Bypasses external filter contexts for date determination.
- Ensures consistent 'latest date' reference.
- Often involves nested CALCULATE with ALL().
Memory trick: To find the true end of time, you must ignore all distractions.