Relative Date Dimension

[Mariusz]

This article explains how to create a Date Dimension Table in Power BI’s Query Editor with an additional set of columns that will allow you to sort report display columns in your Date Dimension, add conditional formatting capabilities, and last but not least, perform relative filtering on current ( 0 ), previous ( -1, -2… ) or future ( 1, 2... ) Date, Week or Month and many more.

Before we start, a lot of functionality described in this post can be achieved by using Power BI’s Relative Date Filter in a Filter Pane or by writing DAX expression, the aim here is to give you an alternative and highlight some extra functionality.  

Create your Relative Date Dimension.

Let’s start with creating our table, below you will find M code that can be copied directly into Advanced Editor of a Blanc Query and attached pbix file containing all the examples described in this article.

let
    // get current date, week, month, quarter and year 
    #"This Date" = Date.From( DateTime.LocalNow() ),
    #"This Week" = Date.WeekOfYear( #"This Date"  ),
    #"This Month" = Date.Month( #"This Date"  ),
    #"This Quarter" = Date.QuarterOfYear( #"This Date"  ),
    #"This Year" = Date.Year( #"This Date"  ),
    
    // get calendar start and dates 
    #"Start Date" = #date( #"This Year" -2, 1, 1 ),
    #"End Date" = #date( #"This Year" + 1, 12, 31 ),

    // create date table
    #"List all Dates" = let 
        #"Duration from Start to End" = Duration.Days( #"End Date" - #"Start Date" ) + 1,
        #"List Dates" = List.Dates( #"Start Date", #"Duration from Start to End", #duration( 1, 0, 0, 0 ) ),
        #"Table Dates" = #table( type table [ #"Date" = date ], List.Transform( #"List Dates", each { _ } ) )
    in
        #"Table Dates",

    // add all columns
    #"Added rDate" = Table.AddColumn( #"List all Dates", "rDate", each Number.From( [Date] - #"This Date" ), Int64.Type ),
    #"Added Week" = Table.AddColumn( #"Added rDate", "Week", each " W" & Text.PadStart( Number.ToText( Date.WeekOfYear( [Date] ) ), 2, "0" ), type text ),
    #"Added rWeek" = Table.AddColumn( #"Added Week", "rWeek", each Date.WeekOfYear( [Date] ) - #"This Week", Int64.Type ),
    #"Added Month" = Table.AddColumn( #"Added rWeek", "Month", each Text.Start( Date.MonthName( [Date] ), 3 ), type text ),
    #"Added rMonth" = Table.AddColumn( #"Added Month", "rMonth", each Date.Month( [Date] ) - #"This Month", Int64.Type ),
    #"Added Quarter" = Table.AddColumn( #"Added rMonth", "Quarter", each "Q" & Number.ToText( Date.QuarterOfYear([Date] ) ), type text ),
    #"Added rQuarter" = Table.AddColumn(#"Added Quarter", "rQuarter", each Date.QuarterOfYear( [Date]) - #"This Quarter", Int64.Type ),
    #"Added Year" = Table.AddColumn(#"Added rQuarter", "Year", each Number.ToText( Date.Year( [Date] ) ), type text ),
    #"Added rYear" = Table.AddColumn( #"Added Year", "rYear", each Date.Year( [Date] ) - #"This Year", Int64.Type ),
    #"Added Year Week" = Table.AddColumn( #"Added rYear", "Year Week", each [Year] & " " & [Week], type text ),
    #"Added rYearWeek" = Table.AddColumn(#"Added Year Week", "rYearWeek", each ( [rYear] * 53 ) + [rWeek], Int64.Type ),
    #"Added Year Month" = Table.AddColumn( #"Added rYearWeek", "Year Month", each [Year] & " " & [Month], type text ),
    #"Added rYearMonth" = Table.AddColumn( #"Added Year Month", "rYearMonth", each ( [rYear] * 12 ) + [rMonth], Int64.Type ),
    #"Added Year Quarter" = Table.AddColumn( #"Added rYearMonth", "Year Quarter", each [Year] & " " & [Quarter], type text ),
    #"Added YearQuarters" = Table.AddColumn(#"Added Year Quarter", "rYearQuarter", each ( [rYear] * 4 ) + [rQuarter], Int64.Type)
in
    #"Added YearQuarters"

The logic behind Relative columns is very simple, to create column [rDate] we simply take our [Date] column and minus current date ( Date.From( DateTime.LocalNow() ) ) from it, to get 0 as an output for today, -1 for yesterday and 1 for tomorrow, we apply the same logic to other ( r ) columns so we can filter weeks, months, quarters and years, is the same way.

How can I use the Relative Date Dimension?
Use rColumns as filters.

In the below scenario you can see how easy it is to filter your orders to include the last three months, current month and next month by simply applying a filter on rYearMonth column where the value is less than or equal to 1 and greater than or equal to -3

In the next scenario, we compare Current Year vs Last Year but only for the months that are fully completed, to achieve this we apply filters on rYear ( 0, -1 ) and rMonth is less than 0, as demonstrated below.

Use rColumns for Conditional Formatting.
In the first example, we have shown, how you can use rYearMonth Column to apply filters to your visual on the last three month, current month and next month, now we're going take it to the next level by applying Conditional Formatting.

As you can see below I've used rYearMonth Column again and applied three rules to make it easy to distinguish between previous, current and next months, what's amazing here, is that I didn't have to use any DAX to achieve this.

Use rColumns in DAX Expressions 

You can use rColumns in your DAX expressions, for example, if you like to calculate the previous week's value, you could write an expression like below. 

Order Value Previous Week = 
VAR _pw = SELECTEDVALUE( 'Relative Date Dimension'[rYearWeek] )
RETURN 
CALCULATE(
    [Order Value],
    ALL( 'Relative Date Dimension' ),
    TREATAS( { _pw -1 }, 'Relative Date Dimension'[rYearWeek] )
)

Another example would be, calculating an average for the previous 4 weeks, it this scenario your expression would look like below.

Order Value Previous 4 Weeks avg = 
VAR _pw = SELECTEDVALUE( 'Relative Date Dimension'[rYearWeek] )
RETURN 
CALCULATE(
    AVERAGE( Orders[Value] ),
    ALL( 'Relative Date Dimension' ),
    TREATAS( { _pw -1, _pw -2, _pw -3, _pw -4 }, 'Relative Date Dimension'[rYearWeek] )
)

Use rColumns as Sort Columns
An additional benefit of creating Relative Columns is, that they can easily replace the sort columns in your Date Dimension, so essentially you are not creating extra columns in your table, they replace the columns you probably ever used for one thing only, and at the same time you open up extra functionality.

Conclusion

Hopefully, the above examples have demonstrated how easy it is to use the Relative Date Dimension and I'm sure that you will find a good use for it in your own data models.

There is one last thing that you need to be aware of while using the Relative Date Dimension. The relative columns will only update when the table is loaded, so you will need to schedule your refresh on at least daily so the rDate column is showing 0 on the right day.

If you have any question, please let me know in the comment section below.

Relative Date Dimension.pbix

[Anonymous]

Hi @Mariusz - 

Thanks for the article. I highly recommend everyone include relative dates in their date dimensions. This seems to be a good place to discuss related ideas. 

1. Name the relative dates. One benefit of this is to create a slicer with "Current Year" selected. The user can easily switch to "Last Year" if desired. Something like this:

    InsertRelativeYearDescription = Table.AddColumn(InsertRelativeYear, "Relative Year Description", 
                                    each if [Relative Year] = 0 then "Current Year"
                                        else if [Relative Year] = -1 then "Last Year"
                                        else if [Relative Year] = 1 then "Next Year"
                                        else if [Relative Year] < -1 then Number.ToText(Number.Abs([Relative Year])) & " Years Back" 
                                        else Number.ToText([Relative Year]) & " Years Ahead")

2. Similarly, we can Group relative dates. The following is a simple version, but customized groupings could also be created to reflect business requirements, such as grouping 5 week periods compared to today's date for comparison purposes.

    InsertYearGroup = Table.AddColumn(InsertRelativeDayDescription, "Year Group", 
                                    each if [Relative Year] = 0 then "Current Year"
                                        else if [Relative Year] < 0 then "Past Years"
                                        else "Future Years")

3. This article refers to relative dates in particular, but it's worth mentioning some other potential enhancements in a date table: Fiscal Periods, workdays and whether a day is before or after a particular event (such as "Are the financial books closed for this period?"). 

Thanks again @Mariusz!

Nathan

[luisccmm]

@Mariusz  This is a great help!!  How would you apply  you relative_dates in orde to calculate YTD growth, this year vs last year for example?

[Anonymous]

This is great! The examples are explanation are very apprecaited.

[dmvega]

Thank you very much for this article! I was trying to use relative days filters, but I have a huge problem with UTC time in server and my local time, but with this solution now I am able to do it!