How to Get Relative Date Filtering in Power BI

Constantly updating the date range in your Power BI reports every week is a tedious, time-consuming chore. Reports should be assets that save you time, not create more manual work. This article will show you how to use Power BI's relative date filtering to create dynamic, "set it and forget it" dashboards that always show the most current data, no manual adjustments required.

What Exactly is Relative Date Filtering?

Think about how you typically ask for a report. You probably say things like, "How did sales do last month?" or "Show me our website traffic for the last 7 days." You're not usually asking for "sales from May 1 to May 31." The date range is relative to today.

That's precisely what relative date filtering does in Power BI. It automatically creates a moving time window based on the current date, so you don't have to manually change the dates to see the latest information.

• An absolute date filter is static, like 01/01/2024 - 01/31/2024. It is fixed and never changes unless you edit it.
• A relative date filter is dynamic, like last 30 days. Every day, this window shifts forward. The report you look at on Friday will show a different 30-day period than the one you saw on Monday.

This simple difference is a game-changer. It transforms your report from a static snapshot into a living dashboard that always reflects what’s happening right now, saving you countless hours of manual updates and ensuring your team is always looking at the freshest data.

Setting Up Your First Relative Date Filter (The Easy Way)

Power BI has a fantastic built-in feature for this that covers most common use cases. You can apply it as a filter behind the scenes to a specific chart (a visual-level filter), an entire report page (a page-level filter), or your whole report (a report-level filter).

Let's walk through setting up a simple filter to show data from the last 30 days on a single line chart.

Step 1: Select Your Visual and Open the Filters Pane

First, click on the visual you want to filter. This might be a bar chart showing sales, a line chart tracking a marketing campaign's performance, or a simple card visual. Once selected, look to the right of your report canvas for the Filters pane. If you don't see it, go to the "View" tab in the top ribbon and make sure "Filters" is checked.

Step 2: Add Your Date Field to the Filter Well

Find your date field in the "Data" pane (this might be named 'Order Date,' 'Signup Date,' or simply 'Date'). Drag this date field and drop it into the "Add data fields here" section under the "Filters on this visual" heading within the Filters pane.

Step 3: Change the Filter Type to "Relative Date"

Once you drop the date field in, Power BI will default to "Basic filtering," which shows a list of all dates. Click the dropdown arrow under "Filter type" and select Relative date from the list. This is where the magic happens.

The interface will change, presenting you with a set of user-friendly dropdown menus to define your time window.

Step 4: Configure Your Relative Date Range

You'll now see three main options to configure your dynamic date range:

• Show items when the value:
• Time value box: Here, you'll type in a number. This works with the "is in the last" or "is in the next" setting. For our example, we want the "last 30 days," so you'd type 30 in this box.
• Time unit dropdown: Here, you select the unit of time for your number. You can choose from 'Days', 'Weeks', 'Months', and 'Years', as well as their 'Calendar' equivalents. We'll select Days for our example.

Your filter configuration should now read: "Show items when the value," "is in the last," "30," "Days."

Step 5: Apply the Filter (and Decide on 'Today')

Once you’ve set the options, click the "Apply filter" button at the bottom of the card if it hasn't already activated. Your visual will instantly update to show only the data from the last 30 days. And that's it! Your chart is now automated.

You'll also notice a handy checkbox for "Include today." If you uncheck it, the filter for the "last 30 days" will include data from yesterday going back 30 days, but will exclude the numbers from the current, incomplete day. This is particularly useful if your data only refreshes overnight and showing partial-day data could be misleading.

Empower Your Users with a Relative Date Slicer

Static, back-end filters are great for standardized reports, but what if you want to give the person viewing the report the power to choose their own date range? This is where the interactive Relative Date Slicer comes in handy.

A slicer is an on-canvas filter that users can click and interact with directly. Changing the slicer immediately updates all the relevant visuals on the page, allowing for quick self-service analysis.

How to Create a Relative Date Slicer:

1. Add a slicer visual to your canvas. You can find the Slicer icon in the "Visualizations" pane. Click it to add a blank slicer to your report page.
2. Drag your date field into the visual. Just like with the filter, grab your date field from the "Data" pane and drop it into the "Field" well of the new slicer visual.
3. Change the slicer style. By default, it will show up as a "Between" range slicer with two date pickers. To change this, select the slicer, then go to the "Format your visual" pane (the paintbrush icon). Expand "Slicer settings," then "Options." Change the "Style" dropdown from "Between" to Relative Date.

Your slicer will transform into the same user-friendly relative date interface you saw in the Filters pane. Now, your colleagues or clients can view the report and easily switch between seeing "last 7 days," "this quarter," or "next 2 weeks" without needing to ask you for a dozen variations of the same report.

Advanced Techniques: Custom Relative Date Logic with DAX

The built-in filters are powerful, but eventually your business will have unique questions they can't answer. What if you need to compare this month's performance to the same month last year? Or what if your internal "week" starts on a Wednesday? For these scenarios, you'll need the power of DAX (Data Analysis Expressions).

DAX is Power BI’s formula language, similar to Excel functions but far more capable, especially for date and time calculations - what analysts call 'time intelligence.'

The Golden Rule: Use a Calendar Table

Before writing a single line of time intelligence DAX, you must have a proper calendar table in your model. This is the foundation of dependable analytics in Power BI.

A calendar table is a dedicated table that contains a complete and continuous list of dates covering your entire data range (e.g., from the date of your first sale to a year in the future). It should also include helpful columns like Year, Quarter, Month Name, Day of the Week, etc. Once created, you connect its date column to the date columns in your business data (like your sales table's 'Order Date').

You can create a basic calendar table in seconds by going to Modeling > New Table and using a formula like Calendar Table = CALENDARAUTO().

Example 1: Calculating "Month-to-Date" (MTD) Metrics

A common request is to show sales for the current month through today. While the built-in "this month" filter gets you close, a specific MTD measure gives you complete control.

Create a few new measures using time intelligence functions:

Cost MTD = TOTALMTD(SUM('Facebook Ads'[Cost]), 'Calendar Table'[Date])
Revenue MTD = TOTALMTD(SUM(Shopify[Revenue]), 'Calendar Table'[Date])

• TOTALMTD() is a function that calculates a cumulative total for the current month.
• SUM() is the base calculation you're performing. It could be any aggregation like SUM, COUNT, AVERAGE, etc.
• 'Calendar Table'[Date] tells the formulas which date column to use for the calculation.

Now, when you add these MTD measures to visuals, they will always show the correct total for the current month, updating every single day automatically as new data comes in.

Example 2: A Custom Flag for the "Past 180 Days"

Suppose you need a filter that precisely includes the past 180 days for a retention report. Here, you can create a calculated column in your calendar table that 'flags' the dates you're interested in.

Go to your Calendar Table, select "New Column," and enter this DAX formula:

Is in Past 180 Days =
IF(
    'Calendar Table'[Date] > TODAY() - 180 && 'Calendar Table'[Date] <= TODAY(),
    "Yes",
    "No"
)

This formula checks every date in your calendar. If a date falls between 180 days ago and today, it gets a "Yes", otherwise, a "No." Now you have a simple filter you can apply anywhere. Drag the new "Is in Past 180 Days" column into your Filters pane (or a new slicer), choose basic filtering, and just select "Yes." Every visual on your report can now be dynamically filtered to this precise, rolling 180-day window.

Common Pitfalls and Best Practices

As you use relative date filters more often, keep these tips in mind to avoid common issues:

• Check Your Data Types: Make sure your date columns are set to a 'Date' or 'Date/Time' data type in the model. A date stored as text won't work with any time intelligence features.
• The TODAY() Function is Your Anchor: The TODAY() function in DAX returns the current date without the time. NOW() returns both date and time. For filtering whole days, TODAY() is almost always what you need. TODAY() updates based on when the dataset was last refreshed.
• Mark as Date Table: After creating your calendar table, right-click it and choose "Mark as date table." This tells Power BI which table to use for its internal time intelligence calculations, ensuring they work correctly.
• Know When to Use What: Don't start with DAX if you don't have to. The built-in relative date filter and slicer are well-optimized and handle a majority of business use cases with just a few clicks.
• Understand Data Refresh: A relative filter is relative to the date of the last data refresh, not the moment you open the report (unless you are using a live connection). If your data only refreshes once a day, "yesterday" will be "today" until the morning refresh is complete.

Final Thoughts

Mastering relative date filtering takes your dashboards from static documents to dynamic analysis tools. By using the simple built-in filters for everyday tasks and adding a bit of DAX for custom needs, you can automate your reporting and spend less time updating charts and more time finding insights.

If you'd rather sidestep Power BI's learning curve for this kind of on-demand analysis, we designed Graphed to be the easiest way to get answers from all your data. After we connect to your sources like Google Analytics or Shopify, you can just ask questions in plain English, such as "show me website sessions in the last 28 days," or "compare revenue for this quarter vs last quarter." Graphed generates the dashboard for you instantly, using live data, so you're always looking at real-time information without configuring a single filter.