cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrCoyado Frequent Visitor
Frequent Visitor

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 to last year's same day of week. Example:

 

2017-01-01, which was a Sunday, we compare to the same Sunday of 2016, which was 2016-01-03.

 

Is there any time intelligence function I can use in order to be able to make this kind of comparison?

 

Best regards,

1 ACCEPTED SOLUTION

Accepted Solutions
MrCoyado Frequent Visitor
Frequent Visitor

Re: Time Intelligence - Comparing Day of Week

Hello @Steve_Wheeler 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.

10 REPLIES 10
Steve_Wheeler Established Member
Established Member

Re: Time Intelligence - Comparing Day of Week

@MrCoyado,

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/

MrCoyado Frequent Visitor
Frequent Visitor

Re: Time Intelligence - Comparing Day of Week

@Steve_Wheeler, 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

Super User
Super User

Re: Time Intelligence - Comparing Day of Week

Hey,

 

interesting question :-)

 

My solution is based on the idea, to create a column in my calendar table that contains values like this:

Sunday - 1 or

Monday - 2

The value "Sunday - 1" marks the 1st Sunday in every year, ...

Then I can use this column to slice / filter my data in related fact tables (related by the date column), for simplicity I also omitted fact tables.

 

I first create a very simple date table (in real life your caledar table must not have missing dates).

 

dates.png

 

then i added columns for the year and the name of the weekday ...

and also an index column (this index column i just used a measure in my little report)

 

year and name of weekdays.png

 

maybe now this becomes a little mind boggling

 

I'm grouping the table by the columns Year and Day Name ...

 

grouping.png

 

the result will look like this ...

 

grouping result.png

 

A closer look at the formula from this step

 

= Table.Group(#"Inserted Day Name", {"Year", "Day Name"}, {{"All Rows", each _, type table}})

 

reveals that nothing happens to each group indicated by the _

 

Now I'm tweaking this formula a little by replacing _ with

 

Table.AddIndexColumn( Table.Sort(_, {"Date", 0 } ) , "IndexInGroup" ,1,1 )

Now an IndexColumn "IndexInGroup" will be created, the rows in each group will be sorted by the date column

 

After expanding the resulting table selecting the interesting columns the result will look like this ...

 

almost done.png

 

The last step will be much more simple just combine the columns "Day Name" and "All Rows.IndexInGroup", using

"Custom Column"

 

= Table.AddColumn(#"Expanded All Rows", "Custom", each [Day Name] & " - " & Text.From([All Rows.IndexInGroup])) 

 

I have my final result ...

 

final result.png

 

A simple report can look like this

 

a little report.png

 

my sample file can be downloaded here ...

https://www.dropbox.com/s/2t8sekfxz57m92s/Create%20Weekday%20Comparisons.pbix?dl=0

 

Hope this helps

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Steve_Wheeler Established Member
Established Member

Re: Time Intelligence - Comparing Day of Week

@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.

MrCoyado Frequent Visitor
Frequent Visitor

Re: Time Intelligence - Comparing Day of Week

Hello @Steve_Wheeler 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.

Steve_Wheeler Established Member
Established Member

Re: Time Intelligence - Comparing Day of Week

It's good to hear you got a solution.

rosre075 Frequent Visitor
Frequent Visitor

Re: Time Intelligence - Comparing Day of Week

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 Frequent Visitor
Frequent Visitor

Re: Time Intelligence - Comparing Day of Week

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 Frequent Visitor
Frequent Visitor

Re: Time Intelligence - Comparing Day of Week

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.