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

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 =
ADDCOLUMNS (
    GENERATE (
        CALENDAR ( “2012-01-01”TODAY () ),
        SELECTCOLUMNS ( GENERATESERIES ( 12 )“Units Sold”RANDBETWEEN ( 15 ) )
    ),
    “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 =
ADDCOLUMNS (
    ADDCOLUMNS (
        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] )
RETURN
    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.
Thanks.


Similar Posts

Leave a Reply

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