DIY 2 : How to achieve Relative Date filtering in Power BI

Table of Contents

Microsoft Power BI Exercise-2 | Working With Relative Dates

In this article, let’s prepare a demo for Relative Date using DAX Expression in PowerBI. Relative Date means When you select a date in Slicer then last n days data get analyzed with respect to any measure. For more details, read the article below.

I got a ping from a friend that he wants only the last 30 days of data in all the visuals on the page based on the date selected in the slicer.

This requirement is very common among clients because in order to see the trend for the last particular number of days we need to use relative dates in Power BI.

Here I have written the article in a step by step manner so if someone wants to practice he/she can practice it very easily by themself.

Step 1: Create a Data Table using DAX Expression.

Here, We need two tables for example here.

1. Data Table

Using the below DAX Expression you can create a data table.

Data Table =
        CALENDAR ( “2012-01-01”TODAY () ),
    “Month”DATE ( YEAR ( [Date] )MONTH ( [Date] )1 )

2. Dates Table

We also require the date table here for this requirement. We can create Date Table using the below mentioned DAX Expression

Dates =
        CALENDAR ( “2016-01-01”TODAY () ),
        “Month”DATE ( YEAR ( [Date] )MONTH ( [Date] )1 )
    “Month Inverse Sort Col”, – INT ( [Month] )

Step 2: Data Modelling

Microsoft Power BI Exercise-2 | Working With Relative Dates
Here, Dates Table has been kept isolated so that it does not filter the main data table. Values selected in the slicer are captured in the Dax Expression which is discussed later in the article.

Step 3: Creating Visuals in Power BI Desktop

Microsoft Power BI Exercise-2 | Working With Relative Dates
Here, I have created the Line Graph with Units Sold on the Y-axis and Date on the X-axis. Along with it, I have created a slicer having a Date in it.
These visuals are just for example. In your actual project, there can be any number of visuals having the requirement to show only relative dates.

Step 4: Relative Date Flag Creation

The flag can be created either by making calculated columns or by making calculated measures. The best way is to use calculated measure since it does the calculation on the fly. 
We will be using the Date column present in both Data Table and Dates Table. Please find below Dax expression used for creating a flag.

Relative_date =
VAR Data_date =
    MIN ( ‘Data Table'[Date] )
VAR Days = 30
VAR Datetable_date =
    MAX ( Dates[Date] )
    IF ( Data_date < Datetable_date && Data_date >= Datetable_date – Days10 )

Here Data_date is the variable that is taking value which is selected in the slicer. Datetable_date is another variable that is taking value from the Dates table. For all the values which are less than the selected date but greater than selected date -30 days, the flag is set as 1.

Step 5: Placing the Flag in the Page Visual Level Filter

Microsoft Power BI Exercise-2 | Working With Relative Dates
Now you have to place the Measure Relative_date in the visual level filter with its value as 1. After placing it you will get only those dates for which is less than the selected date in the slicer.
If you like the article, Please share the blog and comment in case of any feedback.

Power BI Desktop
Kaushlendra Mishra

How to create a report quickly in Power BI Service?

Do you want to create a quick report in Power BI? The latest Power BI version has made it possible to create a quick report using data from the web and existing published datasets in Power BI Service. So, now you do need to download the Power BI Desktop software into your PC and directly make a report using the Power BI service.

Read More »
Kaushlendra Mishra

What is Availability Set and Availability Zone in Azure?

What is Availability Set and Availability Zone in Azure? In Availability Set, VM`s copy is created and is isolated with separate physical servers, computer racks, storage units, network switches in a data center within a region so if one of the VM stops working then still we have the copy of the VM working within the same datacenter. While Availability Zone ensures that complete datacenter data is replicated to another place at a distance within the same region so that even if complete data center fails due to any reason then also Availability of data is ensured.

Read More »
Kaushlendra Mishra

How to Prepare for DP-200 Exam?

I have passed the DP-200 exam on 28 Oct 2020. I have gone through the various study material and courses in order to prepare for the exam. Overall, the time taken by me for the DP-200 exam is approx 3 months. Ideally, you should not take 3 months of time but due to lack of guidance, I happen to take a number of online courses which was not required to be taken for passing the DP-200 Exam.

Read More »

Leave a Comment

Your email address will not be published. Required fields are marked *