Forum Discussion
LY same DAYS
- 7 years ago
For this sort of calculation, I would recommend you have a separate Date table, and include Year and DayOfYearNumber columns such that corresponding weekdays from different years have the same DayOfYearNumber.
The DAX Date Template from SQLBI has such a column called FW DayOfYearNumber.
Here is an example I created:
The LY Sales measure looks like this, which you can adapt to your model:
PY Sales = CALCULATE ( SUM ( 'Sales'[Sales] ), TREATAS ( SELECTCOLUMNS ( 'Date', "FW Year", [FW YearNumber] - 1, "FW DayOfYearNumber", [FW DayOfYearNumber] ), 'Date'[FW YearNumber], 'Date'[FW DayOfYearNumber] ), ALL ( 'Date' ) )This measure takes the FW Year & FW DayOfYearNumber combinations from the current filter context, and shifts each year to the previous year.
For this sort of calculation, I would recommend you have a separate Date table, and include Year and DayOfYearNumber columns such that corresponding weekdays from different years have the same DayOfYearNumber.
The DAX Date Template from SQLBI has such a column called FW DayOfYearNumber.
Here is an example I created:
The LY Sales measure looks like this, which you can adapt to your model:
PY Sales =
CALCULATE (
SUM ( 'Sales'[Sales] ),
TREATAS (
SELECTCOLUMNS (
'Date',
"FW Year", [FW YearNumber] - 1,
"FW DayOfYearNumber", [FW DayOfYearNumber]
),
'Date'[FW YearNumber],
'Date'[FW DayOfYearNumber]
),
ALL ( 'Date' )
)
This measure takes the FW Year & FW DayOfYearNumber combinations from the current filter context, and shifts each year to the previous year.