Search this blog:

Search This Blog

Categorical Date Slicer in Power BI

Today's post is inspired by @Iguima of the Power BI Community. You can read their original Date Categorical Slicer question on the Power BI Community Desktop Forum.

The original poster would like to achieve a categorical date slicer that enables the report user to filter the Sales (fact) table for all data AFTER the selected event/date category. Something similar to the final result below:

GIF screenshot Power BI final result

Note the events span over 20 years and include the 911 terrorist attacks on the USA, COVID 19 pandemic and also some not so catastrophic events such as iPhone first release date. I have added a few more recent events for 2020-21 in case you want to test on your own sales data (which might not span 20+ years!).

Event Name Event Date
COVID 19 Outbreak Wednesday, March 11, 2020
Joe Biden wins US Presidency Saturday, November 7, 2020
Taliban take over Kabul Sunday, August 15, 2021
911 Attacks Tuesday, September 11, 2001
iPhone Released Friday, June 29, 2007

For reference, I have highlighted the important parts of the original question: 

screenshot Original Poster's Question in Power BI Community forum

There are two key issues in this request: 

  1. We want to filter for data only AFTER the selected event. Most filter behavior will return data only ON the selected event.
  2. We want this to be responsive to the filter and slicer selections made by the report consumer.

I have already written about key issue number 2 in a previous blog on Reporting Order of Operations. Basically, calculated columns do not respond to report filters and slicers. We need to solve this problem with a MEASURE.

Data Model

Before we can get into the measure and DAX, we need to review the Data Model. Any of my former students will tell you this is the foundation of your Power BI report. Without the correct data model, DAX won't work. 

For this scenario, we want the Important Event to act as a bookend for our Date table, not filter it just for that specific date. Therefore we need to ensure that the Important Events table is NOT related in any way to the other tables in our data model. 

Below I have a very simple data model: Calendar (date dimension) and Sales (fact) with my Important Events (Categorical slicer table) unrelated to any of these.

screenshot Power BI data model

DAX Formula

Now that we have the Data Model sorted, we can move on to the DAX.

We start with a simple measure for Total Sales:

Total Sales =
SUM ( Sales[Sales Amount] )

Use this [Total Sales] measure in a visualization by Calendar[Year].

We then add a slicer for the 'Important Events'[Event Name]. Since the Important Events table has no relationship to our data, this won't do anything in our report. 

In order to get the Total Sales to filter based on the slicer, we need to figure out what value the user has selected. I have chosen to use the MIN function in case multiple events are selected. This will ensure we are looking at all data after the earliest event.

Selected Event Date =
MIN ( 'Important Events'[Event Date] )

NOTE: Because the [Selected Event Date] is a measure, it will respond to user selections in the slicer as per the Reporting Order of Operations blog. 

Finally, we can use this measure as a filter. You may initially be tempted to write the following DAX and get an error: 

screenshot wrong DAX formula

I haven't used CALCULATE within the filter, but I have used [Selected Event Date] measure, and every measure has an implicit CALCULATE function in front of it. Therefore this DAX expression is not allowed. 

Instead, we need to provide a ROW CONTEXT for the calculation, here I use the FILTER function to provide this context. Now, I am able to use the [Selected Event Date] measure (with its hidden CALCULATE function and all) inside the filter expression:

Sales After Selected Event =
    [Total Sales],
    FILTER (
        'Calendar'[Date] > [Selected Event Date]

Hurray! We get the final result we're looking for, as per the GIF at the start of this article. 


You cannot use COLUMNS to respond to filter/slicer selections.

You must not have relationships if you want to do before/after/range filters. 

Once we have overcome these two obstacles, the desired result is achievable. Now to just update any other measures with this added filter. 

Custom Visual Review: Charticulator

This is not your ordinary custom visual - this is EVERY custom visual. Charticulator puts the power to design and develop custom visuals to ...