Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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).
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)
maybe now this becomes a little mind boggling
I'm grouping the table by the columns Year and Day Name ...
the result will look like this ...
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 ...
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 ...
A simple report can look like this
my sample file can be downloaded here ...
https://www.dropbox.com/s/2t8sekfxz57m92s/Create%20Weekday%20Comparisons.pbix?dl=0
Hope this helps
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.
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
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/2017 | Sun | 701 | |||
2/01/2017 | Mon | 101 | 1/01/2018 | Mon | 101 |
3/01/2017 | Tue | 201 | 2/01/2018 | Tue | 201 |
4/01/2017 | Wed | 301 | 3/01/2018 | Wed | 301 |
5/01/2017 | Thu | 401 | 4/01/2018 | Thu | 401 |
6/01/2017 | Fri | 501 | 5/01/2018 | Fri | 501 |
7/01/2017 | Sat | 601 | 6/01/2018 | Sat | 601 |
8/01/2017 | Sun | 702 | 7/01/2018 | Sun | 701 |
9/01/2017 | Mon | 102 | 8/01/2018 | Mon | 102 |
10/01/2017 | Tue | 202 | 9/01/2018 | Tue | 202 |
11/01/2017 | Wed | 302 | 10/01/2018 | Wed | 302 |
12/01/2017 | Thu | 402 | 11/01/2018 | Thu | 402 |
13/01/2017 | Fri | 502 | 12/01/2018 | Fri | 502 |
14/01/2017 | Sat | 602 | 13/01/2018 | Sat | 602 |
15/01/2017 | Sun | 703 | 14/01/2018 | Sun | 702 |
16/01/2017 | Mon | 103 | 15/01/2018 | Mon | 103 |
17/01/2017 | Tue | 203 | 16/01/2018 | Tue | 203 |
18/01/2017 | Wed | 303 | 17/01/2018 | Wed | 303 |
19/01/2017 | Thu | 403 | 18/01/2018 | Thu | 403 |
20/01/2017 | Fri | 503 | 19/01/2018 | Fri | 503 |
21/01/2017 | Sat | 603 | 20/01/2018 | Sat | 603 |
22/01/2017 | Sun | 704 | 21/01/2018 | Sun | 703 |
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.
It's good to hear you got a solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |