DIY 2 : How to achieve Relative Date filtering in Power BI
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
Data Table =
ADDCOLUMNS (
GENERATE (
CALENDAR ( “2012-01-01”, TODAY () ),
SELECTCOLUMNS ( GENERATESERIES ( 1, 2 ), “Units Sold”, RANDBETWEEN ( 1, 5 ) )
),
“Month”, DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)
2. Dates Table
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
Step 3: Creating Visuals in Power BI Desktop
Step 4: Relative Date Flag Creation
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 – Days, 1, 0 )
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
