Forum Discussion
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 Wednesday (2/28/18).
here where I'm at, but its not working...
PY Sales = CALCULATE(sum(TABLE[Sales]),SAMEPERIODLASTYEAR(DATEADD(TABLE[Date],-1,DAY)))
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.
7 Replies
Hi shinolalady ,
Try this
CALCULATE ( SUM ( FactSales[SalesAmount] ) , SAMEPERIODLASTYEAR ( PREVIOUSDAY ( DimDate[DateDescription] )))Let me know if you have any questions.Regards,Pavan Vanguri.this seems to only show the previous day, I want to be able to reflect the same set of days from the prior year, based on the filtered time frame selected.
So, if I"m looking at the last 7 days, 4 days, or even the last month I would like a metric that then reflects that same day range from 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.
Hi,
I do not understand. Same date last year should be 2/27/18. Why should it be 2/28/18?
From shinolalady 's original post, I believe the intention is to compare any given date with the day of the previous year with the same day number, with the numbering always starting from the same weekday, so Mon-Fri is always compared with Mon-Fri for example.
This can be achieved by numbering the days of any given year starting from a consistent weekday. I used the SQLBI Dax Date Template with Sunday being the first weekday, and it appeared to give the correct result in this case.
In my example, "2019" begins on Sunday 2018-12-30, and "2018" begins on Sunday 2017-21-31.
Under this numbering, 2019-02-27 and 2018-02-28 are both day #60
shinolalady could you confirm this is what you want?
Oh and I realised I totally ignored the "yesterday" part of your requirement...but this can be added on top.
- Correct exactly what I’m looking to do
- 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”
