Forum Discussion
Filtering Visuals over Dynamic Period
Good Day All, I am trying to create a measure (or column) that will allow me to filter a visual (line chart) over a specific date period. What I am trying to do is as follows:
Start Date: Previous 2 years (from current year)
Latest Date: End of Previous Month (From current month)
I use a Date Table with Date, Years, Month, yymmm, etc.
The data table has a DateReceived column and complaint type column
The visual (line chart) I'm trying to filter has an X-Axis with YearMnth (yymmm), a Y-axis with number of complaints received (count of date received).
All I want it to show is the X-Axis from 22Jan - 24Aug for this month, next month 22Jan - 24Sep, following month, 22Jan - 24Oct, etc.
From January 2025 it will filter to: 22Jan - 24Dec, February 2025, 23Jan - 25Jan etc.
Hopefully this makes sense. The filter must always include the previous 2 years and the current year previous month to date. Would appreciate any assistance. I currently use the Date filter for relative period which I have to update manually every month. I'm trying to automate it.
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.
6 Replies
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.
Good Day suparnababu8, worked perfectly thanks. Simple way to create the respective measure and then add the measure to the filters. Much appreciated.
Basilr57 , if you are not selecting any date in slicer. You can create a measure like below, and use that in visual or visual level filter.
You can also use a relative date slicer or filter https://learn.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Measure =
var _max = eomonth(today(), -1)
var _min = eomonth(_max, -24) +1
return
calculate(Sum(Table[Qty]), filter(Date, Date[Date] >= _min && Date[Date]<= _max) )
in case you need same with selected value
Power BI: Create a 12-Month Trend with Single Slicer Selection on connected Date table- https://www.youtube.com/watch?v=7dPrPk6LPYU&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=2
Good Day amitchandak, thanks for the response. Unfortunately the measure you suggested does not work for me. If I use the measure it only displays some of the data, intermittent moths. The attached image shows what I actually need. The filter is for the previous 32 months (calculated from previous month backwards). Next month (October) I will have to manually change this to 33 months and that is what I want to avoid/automate. But this must only display for the previous 2 years and the year to date, up to previous 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,
ZhuIf 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.
