Forum Discussion
Filtering Visuals over Dynamic Period
Hi Basilr57
To automate the filtering of your line chart in Power BI to always show data from the previous 2 years up to the end of the previous month, you can create a measure that dynamically calculates the date range. Here’s how you can do it:
Create a Measure for the Start Date: This measure will calculate the start date as the first day of the month, two years prior to the current date.
StartDate = DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), 1)- Create a Measure for the End Date: This measure will calculate the end date as the last day of the previous month.
EndDate = EOMONTH(TODAY(), -1) - Create a Measure to Filter the Date Range: This measure will be used to filter the data based on the calculated start and end dates.
DateRangeFilter = IF( MAX('DateTable'[Date]) >= [StartDate] && MAX('DateTable'[Date]) <= [EndDate], 1, 0 ) - Apply the Filter to Your Visual: Use the DateRangeFilter measure as a visual-level filter in your line chart and set it to show only values where DateRangeFilter
Here’s a step-by-step guide to implement this:
Create the Measures:
- Go to the Modeling tab in Power BI.
- Click on New Measure and create the StartDate, EndDate, and DateRangeFilter measures using the DAX formulas provided above.
Add the Measures to Your Visual:
- Select your line chart visual.
- Drag the DateRangeFilter measure to the Filters on this visual pane.
- Set the filter to show only when DateRangeFilter is 1.
This setup will ensure that your line chart always displays data from the previous 2 years up to the end of the previous month, automatically updating each month.
Thanks for the reply from amitchandak.
Hi Basilr57 ,
According to your problem description, I realized the visual is filtered according to the dynamic time period, you can make some more adjustments according to your specific needs, here are my steps:
1.Here's my test data:
2.Create a measure, use 2025 as the cut-off year for analysis and filter out the data within the eligible date range.
dateflag =
var _currentdate=TODAY()
VAR _max=IF(_currentdate<DATE(YEAR(_currentdate),MONTH(_currentdate),24),DATE(YEAR(_currentdate),MONTH(_currentdate)-1,24))
VAR _min=DATE(YEAR(_max),1,22)
VAR _max2025=DATE(YEAR(_currentdate),MONTH(_currentdate)-1,25)
VAR _min2025=_max2025-2
RETURN
SWITCH(
TRUE(),
_currentdate < DATE(2025, 2, 1) && SELECTEDVALUE('Table'[ReceivedDate]) >= _min && SELECTEDVALUE('Table'[ReceivedDate]) <= _max, 1,
_currentdate >= DATE(2025, 2, 1) && SELECTEDVALUE('Table'[ReceivedDate]) >= _min2025 && SELECTEDVALUE('Table'[ReceivedDate]) <= _max2025, 1,
0
)
3.Filtering visual using the measure just created:
4.The final result is as follows, today, for example, you can see that the x-axis ranges from 2024.1.22 to 2024.8.24:
Best Regards,
Zhu
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Good Day v-linhuizh-msft, thanks for the response. The measure you suggested does work but only displays data from January 2024 onwards. I want the display to go back to January 2022 and forwards to previous month from today. Also, you set the maximum date to 2025. What if I want it for future dates going forward? The image below is simply what I am displaying. Previous 32 months which I would have to update manually every month. This is what I want automated. Only show the last 2 years and year to date previous month.
