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.

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