Forum Discussion

MrCoyado's avatar
MrCoyado
Frequent Visitor
9 years ago
Solved

Time Intelligence - Comparing Day of Week

Hi everyone!   I am working with Power BI for awhile now and we are slowly migrating our KPIs from the good old Excel to Power BI. One type of comparison we often make is comparing a day of week t...
Need Help
Tutorial Requests
  • MrCoyado's avatar
    MrCoyado
    9 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

MrCoyado,

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.

rosre075's avatar
rosre075
Frequent Visitor
8 years ago

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/2017Sun701   
2/01/2017Mon1011/01/2018Mon101
3/01/2017Tue2012/01/2018Tue201
4/01/2017Wed3013/01/2018Wed301
5/01/2017Thu4014/01/2018Thu401
6/01/2017Fri5015/01/2018Fri501
7/01/2017Sat6016/01/2018Sat601
8/01/2017Sun7027/01/2018Sun701
9/01/2017Mon1028/01/2018Mon102
10/01/2017Tue2029/01/2018Tue202
11/01/2017Wed30210/01/2018Wed302
12/01/2017Thu40211/01/2018Thu402
13/01/2017Fri50212/01/2018Fri502
14/01/2017Sat60213/01/2018Sat602
15/01/2017Sun70314/01/2018Sun702
16/01/2017Mon10315/01/2018Mon103
17/01/2017Tue20316/01/2018Tue203
18/01/2017Wed30317/01/2018Wed303
19/01/2017Thu40318/01/2018Thu403
20/01/2017Fri50319/01/2018Fri503
21/01/2017Sat60320/01/2018Sat603
22/01/2017Sun70421/01/2018Sun703
  • rosre075's avatar
    rosre075
    Frequent Visitor
    8 years ago

    Hi all :0)

     

     

    I have let go the 'week day index' and simply use the dateadd function for the closest day (same 'day of week') :

     

    Actual_364days earlier = CALCULATE([Actual], DATEADD('Calendar'[Date], -364, DAY))

     

    I am a bit in doubt on this one as it looks so simple! Please let me know if I am wrong. Thanks. 

     

    Rebecca

    • MrCoyado's avatar
      MrCoyado
      Frequent Visitor
      8 years ago

      Hi rosre075!

      I might be mistaken, but leap years should be a problem with this formula.

      Check out if the result works when you have a leap year.

      • rosre075's avatar
        rosre075
        Frequent Visitor
        8 years ago

        MrCoyado Thanks for your feedback. Since my purpose is to compare say the current Monday vs the Monday of the same period last year, I think this dateadd - 364day formula should be fine.

         

        By the way, I found that the dayweek index suggested earlier is really handy and I have used it as the x Axis variable in the visual for this LY measure vs current value. 

         

        Thank all for your valuable advices :)

         

        Rebecca