Relative Date Filters in Power BI / DAX / Power Query

A few years ago (in march of 2013!), I wrote a guest blog post on Rob’s blog (PowerPivotPro) on how to do relative date filters.

The title of that post was “how to always show Yesterday, Today, or Tomorrow’s Data”  with DAX in PowerPivot back in Excel.

The post tried to address the issue that you couldn’t do date filters inside of PowerView and how / when the TODAY() and NOW() DAX functions get evaluated for Calculated Columns and Measures.

To this day, I often use a variation of that method for relative date filters on a report to always display things like:

• Current Year
• Current Month
• Current Week
• Yesterday’s sales
• Last 7 days performance

It’s a simple yet powerful solution that improves the User Experience –  An executive would only need to open a report and he’ll immediately see the data that he wants to see. No need to go through a slicer / filter to get to where he wants to.

We’re now in 2019 and I’ve never addressed new approaches, so here’s my take after 6 years!

Method 1: Using DAX Functions

This is pretty much the same pattern that I created for that blog post over 6 years.

It’s still a pretty viable option but, before you even begin, you need to have a Calendar Table. You can read this full pattern on how to create one with Power Query

Once you have a Calendar Table set up, you can either:

• Create a Calculated Column – only get’s evaluated when the table needs to be evaluated
  • A formula like =[Date Column] – TODAY() would yield a positive number for the days ahead of today and a negative number for the days prior to today’s date and with those values you can use either create a relationship with another table to decode the 0 to TODAY, –1 for YESTERDAY and 1 for TOMORROW or do some sort of BINDING
• Create a Measure – gets evaluated every time your visual needs to be evaluated again
  • with a filter like Calendar[Date]= TODAY() or using TODAY() in conjunction with other time intelligence functions to evaluate the TODAY() value and use it as you please in your Filter Context

While this is still a valid approach, using the next 2 methods is usually a better idea even if you’re still using PowerPivot in Excel 2010.

Method 2: Using the Relative Dates Slicer Filter in Power BI

This is pretty easy inside of Power BI where you can just drag a date field and turn on the “Filter” visual:

then you can change that date filter into a Relative Date filter:

and last but not least just make the changes as to how you want your relative date filter to work:

You can read the full official documentation about this here.

This is a quick and easy to implement solution in the event that you want to do relative dates with Power BI.

I usually recommend people to give this a try if their requirements are not complex.

Method 3: Using Power Query / M

What the previous methods do by default is that they figure out what the local time zone is and go from there. This might be a good idea if you’re working locally and the timezone is exactly the same as the one that report dates and data has been stored.

but…

• WHAT IF your machine is on a different timezone?
• WHAT IF your data is on a completely different timezone than yours?
• WHAT IF the Power BI service is the one refreshing the file and you don’t know their server timezone?

Those are A LOT of WHAT IF scenarios to handle, and none of the previous methods can handle the time zone from a dynamic standpoint. You’d need to figure out what the timezone is and then do a manual offset against it.

Here’s where the Power Query Magic comes in. With Power Query you can absolutely do wonders when it comes to create a Calendar Table and you’ve probably seen that before here.

The concept is to add a custom column to your Calendar Table using the following Formula:

Number.From (  Date.From(DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), #”TimeZone Offset” )) – [Column1])

I’ve highlighted the 2 most important parts of that code.

DateTimeZone.UtcNow() will always calculate the current date and time based on the UTC 0 timezone

DateTimeZone.SwitchZone will do just that. Switch the timezone to whatever your timezone might be. In my case I’m using a Parameter that I call “Timezone Offset” and it can be a numeric value.

Here’s the full code for that query:

let
Source = List.Dates( #date(2017,1,1), Number.From( #date(2025,12,31) – #date(2017,1,1) ), #duration(1,0,0,0)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Inserted Age” = Table.AddColumn(#”Converted to Table”, “DaysFromToday”, each Number.From (  Date.From(DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), #”TimeZone Offset” )) – [Column1], type  number))
in
#”Inserted Age”

Don’t forget that Custom Columns will only get evaluated once whenever you refresh the query, so in the event that you need this to be calculated every time that you need to query a visual on your final report, you’ll need to use the DAX method with a measure, but I highly recommend that you don’t since these functions described here are only available in Power Query.

The beauty behind this last method is that you can figure out the timezone and change the way it behaves quite nicely with just the native functionality.

This technique can also help you to calculate anything that has to do with a dynamic date.

How many days from X date? You can even use it to get the minutes and seconds if you ever have a need for it.

I also noticed that there’s a new blog post on the PowerPivotPro blog that was posted a few weeks ago around this topic as well about how to handle Daylight savings using this same technique.

It took me 6 years to write the follow up to my original post, but it’s finally here