Forum Discussion
Time Intelligence - Comparing Day of Week
Hello Anonymous and TomMartens!
Thanks for your contributions, it helped me a lot.
I used both of your ideas to combine it with my own. Using the Index, I could create the comparisons within a certain date range.
For Easther though, I still need to create the date alignment differently, since it's a fixed date which I need to compare to my previous Easter (we have a 45 day calendar for this campaign), that's where a Dim_Calendar_Campaign helped me out. There I can fix the dates I want to compare (2017-04-16 x 2016-03-27), using the Index to indicate whether the date is in the actual year or in the previous year.
That's an intriguing way to monitor things - "comparing a day of week to last year's same day of week."
Assuming you have a Date table, you can you use DATEADD for 364 days (i.e. 7 days x 52 weeks) - e.g.
Measure Same Day Last Year = CALCULATE ( [Measure], DATEADD ( DateTable[Date], -364, DAY ) )
Note: if you're using it in a Matrix etc. you may have to deal with the 'contiguous date problem' - see https://www.powerpivotpro.com/2014/01/defanging-the-contiguous-date-selections-error/
Anonymous, thanks for the reply!
I work at a chocolate retail business, and our campaigns are highly affected by calendar comparisons and date aligments - e.g. Easter (always Sundays).
I will try it out. My only doubt is whether a leap year will affect the date alignment eventually.
Another solution we are using is to have a Campaign_Calendar table to align dates. The issue here is I cannot run two campaigns simultaneously, since my FactSales table and my Dim_Campaign_Calendar table won't have single values (only if restricted directly in the query) to create a relationship between them. Another limitation of this model is the dependancy on the creation of those alignments.
I guess there is no perfect world yet hehe. Anyway, thank you again for helping out!
Regards
BI for chocolate - I like it! For Easter though, can't it move from late March to late April, depending on a lunar calendar?
And leap years may cause grief - there are 52.xxx weeks per year aren't there?
I wasn't sure what you meant about using your Dim_Campaign_Calendar table, but I like the Date index idea from TomMartens as an alternative. I had started down that path too before thinking it might not be necessary. Maybe it is.
My index approach calculates a number (more efficient than text), from the DayOfWeek and some maths on the DayOfYear:
//Add Day-Week Index Column, to compare same day each year (e.g. first Sunday each year = 601, second Monday = 2 (i.e. 002)) DayWeekIndex= Table.AddColumn(<YOUR LAST STEP> , "DayWeekIndex", each Date.DayOfWeek([Date])*100 + Number.IntegerDivide((Date.DayOfYear([Date])-1), 7) + 1),Power Query has a Date.WeekOfYear function, but it seems set to always start on a Monday which I found harder to include in the calculation above.
Have fun - I'm curious to hear which approach works best for you.
Hi Steve,
We too, need to compare data by day of week, as weekends are in general busier than weekdays.
I have applied your calculation to my calendar for the day_wk column, thanks for sharing your solution.
However, I'm affraid that this calculation might need further modification as I noticed this issue as illustrated below:
when comparing 2018 vs 2017 by day of week using the index with your idea, I found that the 1st Sunday of 2018, ie 7/1/18 will be compared against 1/1/17 instead 8/1/17 which is more relevant.
for comparisons on any two consecutive years, this issue exists (varied on a particular day of week). personally I am not happy comparing dates that are 6 days apart; as a newbie in power bi, I wish for your adivice again on solving this matter.
Many thanks.
Rebecca
1/01/2017 Sun 701 2/01/2017 Mon 101 1/01/2018 Mon 101 3/01/2017 Tue 201 2/01/2018 Tue 201 4/01/2017 Wed 301 3/01/2018 Wed 301 5/01/2017 Thu 401 4/01/2018 Thu 401 6/01/2017 Fri 501 5/01/2018 Fri 501 7/01/2017 Sat 601 6/01/2018 Sat 601 8/01/2017 Sun 702 7/01/2018 Sun 701 9/01/2017 Mon 102 8/01/2018 Mon 102 10/01/2017 Tue 202 9/01/2018 Tue 202 11/01/2017 Wed 302 10/01/2018 Wed 302 12/01/2017 Thu 402 11/01/2018 Thu 402 13/01/2017 Fri 502 12/01/2018 Fri 502 14/01/2017 Sat 602 13/01/2018 Sat 602 15/01/2017 Sun 703 14/01/2018 Sun 702 16/01/2017 Mon 103 15/01/2018 Mon 103 17/01/2017 Tue 203 16/01/2018 Tue 203 18/01/2017 Wed 303 17/01/2018 Wed 303 19/01/2017 Thu 403 18/01/2018 Thu 403 20/01/2017 Fri 503 19/01/2018 Fri 503 21/01/2017 Sat 603 20/01/2018 Sat 603 22/01/2017 Sun 704 21/01/2018 Sun 703
