A data modeler is building a semantic model in Microsoft Fabric. The model needs to include a measure that calculates the 'Total Sales' for the current month, but only for products that had sales in the *previous* month. The 'Sales' table contains 'SaleAmount' and 'SaleDate'. A 'Date' dimension table is also available. Which DAX pattern should be used to achieve this calculation?
- ACALCULATE(SUM(Sales[SaleAmount]), KEEPFILTERS(Sales[ProductID] IN VALUES(CALCULATETABLE(Sales, PREVIOUSMONTH(Date[Date]))[ProductID])))
- BCALCULATE(SUM(Sales[SaleAmount]), FILTER(ALL(Sales), Sales[SaleDate] >= STARTOFMONTH(TODAY()) && Sales[SaleDate] <= ENDOFMONTH(TODAY())))
- CCALCULATE(SUM(Sales[SaleAmount]), MONTH(MAX(Date[Date])) = MONTH(TODAY()))
- DCALCULATE(SUM(Sales[SaleAmount]), PREVIOUSMONTH(Date[Date]))
Show answer & explanationAnswer & explanation
Correct answer: A. CALCULATE(SUM(Sales[SaleAmount]), KEEPFILTERS(Sales[ProductID] IN VALUES(CALCULATETABLE(Sales, PREVIOUSMONTH(Date[Date]))[ProductID])))
This complex requirement needs to first identify products with sales in the previous month and then use that filtered list of products to calculate current month sales. Option C uses `CALCULATETABLE` with `PREVIOUSMONTH` to get the products from the previous month, wraps it in `VALUES` to get distinct ProductIDs, and then uses `KEEPFILTERS` with `IN` to apply this product filter to the current month's sales calculation, ensuring the context remains for the current month.
Why the other options are wrong
- B. This calculates total sales for the current month without considering products sold in the previous month.
- C. This calculates total sales for the current month but doesn't apply the filter for products that had sales in the previous month.
- D. This calculates total sales for the previous month, not current month sales filtered by previous month's products.
DAX Context Transition
Context transition in DAX converts row context into filter context during calculations, often implicitly by `CALCULATE` or explicitly by iterating functions.
- Crucial for complex filtering logic.
- Implicitly occurs with `CALCULATE`.
- Allows filters derived from one period/set to be applied to another.
Memory trick: Filter context shifts and turns, previous month's products, current month learns.