Forum Discussion
Weekday Last Year Comparison
Hi Vvelarde, yep you're right it was 3th July 2015 I confused it with August.
About your answer, It work for this year, but what happen if the year is a leap-year? It has 366 days and a normal year has 365. It have to be dynamic or something like that. My solution have to work to infinity and beyond
One solution that came to mind is on the date (calendar) table
1. create a column WeekNo= Weeknum([Calendar[FullDate]) ( Full Date is the Date column in DD/MM/YYYY format)
2. Create a column WeekDayNo = WeekDay(Calendar([FullDate])
3. Create a column Year = Year(Calendar[FullDate])
4. Create a column DWY = ([WeekDayNo]*100+[WeekNo])*10000+[Year] . This will create numbers like 5012015, 5012016, etc. etc
depending on the Date.
5. Join your fact table to the Calendar table
6. Create a measure sya for Sales = sum(Facttable[SalesAmount])
7. Create a measure to compute the sales for same day same week last year as
SalesLYDWY = Calculate([Sales], Filter(ALL(Calendar),Calendar[DWY] = Max( Calendar[DWY]) -1 ))
8. You should now be able to view the Sales and SalesLYDWY side by side.
Sample of what I did
| Column Labels | ||||||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||||||||
| Row Labels | Sales2 | SWLY | Sales2 | SWLY | Sales2 | SWLY | Sales2 | SWLY | Sales2 | SWLY | Sales2 | SWLY | Sales2 | SWLY |
| 2015 | ||||||||||||||
| 24 | 41 | 43 | ||||||||||||
| 25 | 45 | 47 | 49 | 51 | 53 | 55 | 57 | |||||||
| 26 | 59 | 61 | 63 | 65 | 67 | 69 | 71 | |||||||
| 27 | 73 | |||||||||||||
| 2015 Total | 177 | 108 | 112 | 116 | 120 | 165 | 171 | |||||||
| 2016 | ||||||||||||||
| 24 | 41 | 43 | ||||||||||||
| 25 | 41 | 45 | 43 | 47 | 45 | 49 | 47 | 51 | 49 | 53 | 51 | 55 | 53 | 57 |
| 26 | 55 | 59 | 57 | 61 | 59 | 63 | 61 | 65 | 63 | 67 | 65 | 69 | 67 | 71 |
| 27 | 69 | 73 | 71 | 73 | ||||||||||
| 2016 Total | 165 | 171 | 177 | 108 | 112 | 116 | 120 |
This should work for all years and weeks for eternity.
If this solves your requirement please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Dear Anonymous,
Thank you, this is great.
The only challenge I have is that my last year based on your number. 7 has one drawback.
Net Sales LY test = CALCULATE([Net Sales test];FILTER(ALL('TestDWH Date');'TestDWH Date'[DWY] = MAX('TestDWH Date'[DWY])-1))
When I have it on days, i.e. monday, tuesday etc, my "total" row in f.ex. a table only show value for sunday LY. Do I need any IF statement?
Thank you,
Mikkel
Hi Mikkelnaes
Could you please post your pbix in one drive / google drive and share the link.
Please also explain what is not working correctly.
Cheers
CheenuSing
Hi CheenuSingh,
Just wondering why you would multiply WeekDayNo by 100? Please could you explain?
Thank you
Vijay
Hi VijayReddy
As we want to compare by day by week and year, we had to use a formula to generate the unique number combination.
So,
Create a column DWY = ([WeekDayNo]*100+[WeekNo])*10000+[Year] . This will create numbers like 5012015, 5012016, etc. etc
depending on the Date.
When we use the measure to compute the sales for same day same week last year as
SalesLYDWY = Calculate([Sales], Filter(ALL(Calendar),Calendar[DWY] = Max( Calendar[DWY]) -1 ))
it becomes simpler to just substract 1 from the current selected calendar year.
Cheers
CheenuSing
This dax actually helps me a lot.. Now I can able to compare weekday with previous year same day. In my case I have to write a dax for if both the years has same weekday in a month (sunday with sunday, monday with monday etc.,) then i'm supposed to assingn it as P, If any non comparable day in a month then it should asssign as "T". Also it will workout in the case of leap year. Please help me with the exact dax. TIA
Hello,
I have used your solution, it works great. The only issue is that now I am trying to aggregate it per month as well.
To get the sales for last year I am using this:
Sales Last Year = SUMX(VALUES('Calendar'[DWY]),Calculate(SUM('Sales & Tickets'[Sales]), Filter(ALL('Calendar'),Calendar[DWY] = MAX(Calendar[DWY])-1 )))
Unfortunately when drilling through from day to month data is slightly inaccurate. Any idea how to solve this issue?
Again, thank you for your previous solution, it was extremely helpful!
For those interested, I replaced the MAX by the function EARLIER that I just discovered (which by my understanding we could actually almost always call current row).
That bit on using the EARLIER function to get the right total when using this as a combined measure just made my month! I owe you big time!
Hi Anonymous !
This solution is working perfectly, thank you very much. However, I need to restitute this second date "DWY". I can't achieve it. Do you know how to restitute the date in format "dd/mm/yyyy" ?
Thanks by advance,
Hello! I want to spilt the sales per store, I have 8 stores. I tried your solution but I get the sales for one store. Do you know why is this happening? Has to do with the max? Any solutions? Tnank you!
