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:

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.