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.
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.
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.
It's good to hear you got a solution.