Automatically filter report on actual date with calculated column and with measure

April 11, 2021
Denis Selimovic
Power BI Basicscomment 3Created with Sketch Beta.0 comments

This post is also available in: German


Sooner or later, every Power BI developer will face the question of how to automatically filter a specific visual or the entire report to today’s date.
This can be done in two ways:

  1. With a Calculated Column (always works)
  2. With a measure (works in specific cases)

The approach with a calculated column is much more stable, since measures generally cannot or should not be used as filters. However, if the date exists in the filter context, then this is also possible with a measure, which is why I will present both alternatives.

Filter to today’s date with calculated column

As just said, filtering by calculated column is the more stable of the two approaches. Here, a new calculated column is created in the date dimension, where it is simply checked whether the date in the particular row matches today’s date:

IsToday = IF( 'Date'[Date] = TODAY(), 1, 0 )

In this way, the content of the new calculated column is always “0”, except for the current date. On this day the line shows a “1”:

Column "IsToday" indicates today's date with a "1" as value
Column “IsToday” indicates today’s date with a “1” as value

The new column can now be used to filter single visuals or the whole report on today’s date. As soon as the day changes, the value in the calculated column also changes and the report adjusts to the new date accordingly:

Filtering the report to today's date
Filtering the report to today’s date

Filter with measure on today’s date

A less common method is to filter single visuals by measure. Here, the difference between the date and today is evaluated. If this difference is 0, then it is the current day. The formula is:

IsTodayMeasure = DATEDIFF( MAX( 'Date'[Date] ), TODAY(), DAY )

The only disadvantage, the measure requires a filter context. This means it only works in visuals where the date is present in lines or on an axis, for example. As soon as the date is notavailable as a filter context, as in the case of a card visual, the measure cannot be used for filtering.

I have shown the example here:

The table can be filtered by measure, the card unfortunately not
The table can be filtered by measure, the card unfortunately not

Summary

As just shown, a report can be filtered to today’s date by calculated column with little effort. This method is very stable and has the advantage that the report automatically adjusts to the current date. The possibility per measure exists in certain constellations, if the date column is available in the filter context of the visual. I personally find the option quite elegant, however, it cannot be used generally. Therefore, this is more likely to be classified as a “development feature” to filter a particular visual. However, I would not recommend using this in a productive report.