Forum Discussion
shinolalady
7 years agoNew Member
LY same DAYS
How can i create a measure to calculate PY sales for the same DAY range (rather than DATES)? My data has every individual date of a sale, I'd like to look at Yesterday's sales (2/27/19) vs LY Wednes...
- 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.
Ashish_Mathur
Super User
7 years agoHi,
I do not understand. Same date last year should be 2/27/18. Why should it be 2/28/18?
shinolalady
7 years agoNew Member
I’m looking at a retail calendar where I want to look Monday to Monday which means the “date” won’t be exactly the same, instead I want the same “day”