Filtering Visuals over Dynamic Period

[Basilr57]

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.

[suparnababu8 · Accepted solution]

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:

1. 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:

1. 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.

2. 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.

[Basilr57]

Good Day suparnababu8, worked perfectly thanks. Simple way to create the respective measure and then add the measure to the filters. Much appreciated.

[v-linhuizh-msft]

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!

[Basilr57]

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.

[amitchandak]

@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

[Basilr57]

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.

.