Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Basilr57
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION
suparnababu8
Solution Sage
Solution Sage

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.

View solution in original post

6 REPLIES 6
suparnababu8
Solution Sage
Solution Sage

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.

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
Community Support
Community Support

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:

vlinhuizhmsft_0-1725527630380.png

 

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:

vlinhuizhmsft_1-1725527985466.png

 

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:

vlinhuizhmsft_2-1725528082689.pngvlinhuizhmsft_3-1725528106454.png

 

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.Presentation1.png

 

amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.Presentation1.png

 

.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.