I'm working in Power BI with data in the following format:
| Year Number | Week Number | Sales Value |
|---|---|---|
| 1 | 1 | 20 |
| 1 | 1 | 15 |
| 2 | 5 | 32 |
| ...ect | ...ect | ...ect |
I'm currently using the measure Total Sales = SUM(Table[Sales Value]) and slicers for Year Number and Week Number to allow the user to view total sales for a selected year and week number.
What is the best way to also return the total sales value for the same week number in the previous year (i.e. week number = same, year number = selected - 1)? In excel it would be almost trivial to do with a SUMIFS formula, but I'm seriously struggling to find anything on how I should do it with DAX.
1 Answer
The CALCULATE formula lets you mess with the filter context. Parameters after the 1st override the filter for the specified field. So for your scenario I would use:
Total Sales Prior Year =
VAR v_Current_Year = MAX ( Table[Year Number] )
RETURN CALCULATE ( [Total Sales], Table[Year Number] = v_Current_Year - 1 )The VAR ... RETURN ... syntax lets you elegantly catch a VARiable value from the current filter context, then RETURN a result using a CALCULATEd filter context.
By far the best documentation site is dax.guide. As well as the usual syntax info, they include links to articles and an interactive playground. The authors are the widely acknowledged gurus of DAX.
1
