A Power BI developer is working on a sales report. They have a 'Sales' table and a 'Date' table. The 'Sales' table has a 'SaleDate' column, and the 'Date' table has a 'Date' column, which is marked as a date table. A one-to-many relationship exists from 'Date[Date]' to 'Sales[SaleDate]'. The developer needs to calculate the 'Total Sales' for the current year, but the 'SaleDate' column sometimes contains future dates due to pre-orders. The 'Current Date' is defined by the latest date available in the 'Date' table, not the system date. Which DAX expression correctly calculates the 'Total Sales' for the current year based on the latest date in the 'Date' table?
- AVAR CurrentYear = YEAR(MAX(Date[Date])) RETURN CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Date), YEAR(Date[Date]) = CurrentYear))
- BCALCULATE(SUM(Sales[Amount]), ALL(Date), YEAR(Date[Date]) = YEAR(MAX(Date[Date])))
- CCALCULATE(SUM(Sales[Amount]), YEAR(Sales[SaleDate]) = YEAR(MAX(Sales[SaleDate])))
- DVAR LatestDate = MAX(Date[Date]) RETURN CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(YEAR(Date[Date]) = YEAR(LatestDate)))
Show answer & explanationAnswer & explanation
Correct answer: A. VAR CurrentYear = YEAR(MAX(Date[Date])) RETURN CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Date), YEAR(Date[Date]) = CurrentYear))
To correctly calculate sales for the current year based on the latest date in the 'Date' table, we first need to determine that year. 'MAX(Date[Date])' gets the latest date. Then, 'YEAR(MAX(Date[Date]))' extracts its year. The 'FILTER(ALL(Date), ...)' construct is crucial: 'ALL(Date)' removes any existing filters from the 'Date' table, ensuring we consider all dates to find the 'CurrentYear' and then apply a new filter to include only rows where the year matches our 'CurrentYear' variable, correctly calculating the sum of sales for that specific year.
Why the other options are wrong
- B. While 'ALL(Date)' removes filters, applying 'YEAR(Date[Date]) = YEAR(MAX(Date[Date]))' within CALCULATE without a proper filter modifier (like FILTER) might not work as intended for a table expression to set the year filter correctly. The 'ALL' function used this way would remove all filters from the Date table, then try to apply a filter based on the maximum date, which is inefficient and potentially incorrect.
- C. This uses 'MAX(Sales[SaleDate])', which might include future pre-order dates in the 'Sales' table, not necessarily the latest valid date in the 'Date' table, and doesn't remove existing filters on 'Sales'.
- D. KEEPFILTERS preserves the existing filter context, which is not desired here as we need to explicitly set a new filter for the 'CurrentYear' across the entire 'Date' table, ignoring previous filters. Also, 'YEAR(Date[Date]) = YEAR(LatestDate)' used directly as a filter argument in CALCULATE would not remove existing filters on the Date table.
DAX Filter Context Management
DAX filter context management involves explicitly controlling which data is visible to an expression using functions like CALCULATE, ALL, ALLEXCEPT, FILTER, KEEPFILTERS, and variables (VAR/RETURN).
- CALCULATE is fundamental for changing filter context.
- ALL removes all filters from a table/column.
- FILTER iterates a table and returns rows that meet a condition.
- VAR/RETURN helps store intermediate results and improve readability/performance.
- Context transition (row to filter) happens implicitly in CALCULATE.
Memory trick: Time intelligence in DAX: CALCULATE, ALL, FILTER, VAR/RETURN for precise date filtering.