Override date filter in Power BI

Recently, a friend of mine came across an interesting request he got from his client. They needed to see values for the specified period in the past, based on dates selection.

For example, if they select March 31st 2020, they need to see values for the previous 12 months, so starting from April 1st 2019. Additionally, they need an option to choose a specific date and see values from the beginning of the year for the selected date. Again, if they select March 31st 2020, they need to see values starting from January 1st 2020. Visuals would be adjusted to dynamically change based on user selection.

That being said, Edit Interactions, as a way to remove a filter from specific visual, was not an option, since it would completely deny a user from selecting any specific value for filtering.

Tricky part

The trickiest thing here is establishing a proper relationship between the Date dimension and fact table. Because, if you rely on a “normal” relationship between Date dimension and fact table, data will be filtered based on dates selection which serves as a relationship between these two tables.

Let’s head over to Power BI Desktop and see if something can be done.

As you see in the Model view above, DimDate and FactOnlineSales are connected with the DateKey column. So, as long as I’m selecting dates in the slicer, my bar chart shows only those values filtered by slicer selection, as you see in the following screenshot.

The main question here is: how can we “override” values passed from Date slicer.

Key to solution

The key thing here is to disconnect the “regular” relationship between the Date dimension and fact table. We need an independent, disconnected Dates table, which will serve to define the time frame we need for displaying values in bar chart visual.

Therefore, I will create a new table with the following definition:

Dates = DISTINCT(FactOnlineSales[DateKey])

This will include all the dates from our fact table. This table stays disconnected from our fact table, as you can see in the Model view:

Now, let’s create a new measure, which will calculate sales amount within dates we specify:

Sales Amt = 
         VAR MaxDate = MAX(Dates[DatesDateKey])
         VAR MinDate = CALCULATE(MIN(Dates[DatesDateKey]),
                                ALLEXCEPT(Dates,Dates[DatesDateKey])
                                )
            VAR SalesAmt = CALCULATE(SUM(FactOnlineSales[SalesAmount]),
                              FILTER(FactOnlineSales,FactOnlineSales[DateKey] >= MinDate && FactOnlineSales[DateKey] <= MaxDate))
            RETURN

            SalesAmt

What we are basically doing here is the following: we are defining variable which will select the last selected date (MaxDate). Then, we are defining our starting point: in this case, it will find the first date in Dates table, overriding the existing filter context with ALLEXCEPT function. Finally, we are calculating SalesAmt value using the FILTER function, in order to limit the time frame based on dates set in variables. After we drag a new measure to bar chart visual, we are getting the following result:

So, if I select quarter 4 of the 2008 year, I will see all values from the beginning (first date value in our fact table), up until the selected period. If I had selected quarter 3 of 2009, I would have got the following values:

Pretty cool, ha?

Fine Tuning our solution

However, what if we need to see only those values since the beginning of the year for the date selected, or 12 months prior to the selected date.

The disconnected table stays in place, because, don’t forget, it is the key ingredient to solve this. We will just slightly change our measure for calculating Sales Amt.

If you want to see all values from the beginning of the year, you can adjust measure like this:

Sales Amt StartYear = 
        VAR MaxDate = MAX('Dates'[DatesDateKey])
        VAR MinDate = STARTOFYEAR('Dates'[DatesDateKey])
        VAR Result = CALCULATE(
                            SUM(FactOnlineSales[SalesAmount]),
                            FILTER(FactOnlineSales,
                                FactOnlineSales[DateKey] >=MinDate && FactOnlineSales[DateKey]<=MaxDate)
        )
        RETURN

        Result

So, the only difference is in the MinDate variable definition. Here, we use DAX function STARTOFYEAR to get the first date of the year for the date which was selected.

Another option enables you to see trailing periods you define based on your needs: following example shows the last 12 months, but you can easily modify this:

Sales Amt -12 Months = 
        VAR MaxDate = MAX('Dates'[DatesDateKey])
        VAR MinDate = DATE(YEAR(MaxDate),MONTH(MaxDate)-12,DAY(MaxDate))
        VAR Result = CALCULATE(
                            SUM(FactOnlineSales[SalesAmount]),
                            FILTER(FactOnlineSales,
                                FactOnlineSales[DateKey] >=MinDate && FactOnlineSales[DateKey]<=MaxDate)
        )
        RETURN

        Result

Again, the only difference is in the starting point definition. With a combination of DATE and MONTH functions, we are telling our measure how far in the past we want to calculate. In this case, it’s -12 months, but you can also use years, quarters, days as criteria.

In the illustration above, you can see the example for the beginning of the year calculation, while the next screenshot illustrates calculation for the -12 months:

Conclusion

As you saw, we can override regular behavior of filters using some non-standard techniques, like creating custom disconnected tables.