Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrCoyado
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

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.

View solution in original post

11 REPLIES 11
TomMartens
Super User
Super User

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

 

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hi Tom,

    I am using your steps for comparing weekdays of current year with previous year but i cant able to understand the formulas you have used here:

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

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

 i am getting an error while using this formulas, could you explian about it please.

Anonymous
Not applicable

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

@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

Anonymous
Not applicable

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

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

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

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.

@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

 

 

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
Not applicable

It's good to hear you got a solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.