Share via

Power BI: Filter current period dynamically

  • Article

Overview

Power BI allows the discovery of amazing insights through interactive data visualization. In order to do that, many traditional BI calculation relies on time intelligence, in order to perform a comparison with different time periods and time horizons. A very common question of business decision makers is to compare the "Current Period" with "Last Period" or any other past period. In order to do that, we need PowerBI to be aware at query time of the "Current Time", and bring it back as a dimension to be included in our visualization

Creating a New Column

In this example, we will show to create a column, able to filter your reports by "Current Year", "Past Year" and "Past Years", however you can use the same logic to filter Current Month, Current Day, etc.

First, we need to create the new column by right-clicking over the table that contains the Time Dimension overt the one you want to create the filter:

Now you can use a small piece of DAX in order to create the new variable as follows:

YearPeriod = IF(YEAR(Table[Date])=YEAR(NOW()),"Current Year",IF(YEAR(Table[Date])=YEAR(NOW())-1,"Last Year","Past Years"))

Using the new Column in your reports

Now you can use the new dynamic column in many different ways, and see how it changes dynamically as times goes on. In the image below, the visualization options are shown:

  1. Horizontal Slicer
  2. Matrix
  3. Bar Chart

Summary

PowerBI allows the creation of new columns that can help you to create new dimensions at query time, allowing different Time Intelligence Dimensions as shown above. The example shows how to create a new variable that allows filtering dynamically your reports by the "Current Year", "Last Year" and "Past Years", however, you can extend this to new time periods (Week, Month, etc), or any other scenario. We look forward to hearing your ideas and feedback.