Forum Discussion
Time Intelligence - Comparing Day of Week
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.