Use a variable on "Filters on this virtual"

[kobkabnaja]

Hi community,

I have a quick question about applying a filter to my Power BI dashboard. I want to display monthly data for only the past four years. Currently, I am using a filter at the visualization level to exclude other years, but I would like to make this process dynamic.

Ideally, I would like to set up a filter using a variable like MAX(FY) - 4 (e.g., 2025 - 4 = 2021) so that the chart automatically updates each year. However, so far, I have not been able to figure out how to implement this at the visualization level (please see the attached screenshot for reference).

I am wondering if there is possible in Power BI? And if not, what are some typical approaches for creating dynamic date filters? I would appreciate any advice/suggestions the community may have.

Thank you,

K

 

[kobkabnaja]

Hi @hnguy71 ,

Thank you so much for sharing that example! I will keep it in mind for future projects.

I have a quick follow-up question about the first method. I tried following the link to set up the variable, but I am running into an issue. Despite entering all the data correctly (I believe), the if-else statement is not producing the expected result (see attached). I also tried searching on ChatGPT, but it returned the same approach I am currently using.

Do you have any additional suggestions on how I might resolve this?

Thanks again for your help!
K

[hnguy71 · Accepted solution]

Hi @kobkabnaja ,

Because you're evaluating each year line by line, the current max item will always be equal to the selected item, therefore you'll get a true result always.

This should work instead:

Selected Years = 

VAR _HowManyYrs = 4
VAR _MaxYear = CALCULATE(MAX('DATE'[Year]), ALL('DATE'))
RETURN
IF( SELECTEDVALUE('DATE'[Year]) <= _MaxYear - _HowManyYrs, 0, 1)

Then on your visual, don't forget to filter for where value is 1:

[kobkabnaja]

Hi @hnguy71 ,

Apologies for the delayed response, and thank you so much for this solution—it works perfectly! I will take some time to go through the commands and will reach out if any questions come up.

For anyone else looking for a solution, I highly recommend checking this out. @hnguy71 has also provided an example for using a disconnected filter, which I found useful and plan to incorporate into future projects. Kudos to you, @hnguy71 !

Cheers,
K

[sevenhills]

@hnguy71 explanation and details should work for you!

As you are getting into some issues, check this video also:
https://www.youtube.com/watch?v=44fGGmg9fHI

The video is similar to what @hnguy71 explained. Sharing video as it helps you to visually understand.

[hnguy71]

Hi @kobkabnaja 

That's possible. You can create a new measure and evaluate if Year >= 4, or a dynamic thershold. If it is, return 1, else 0. Then add the measure to your visual and filter for where value = 1.

Honestly, though, I would use a disconnected slicer table to retrieve the year selected and then build all my measures against my date table.

[kobkabnaja]

Hi @hnguy71,

Thank you for the quick response! I came across this method during my search but wasn not sure how the variable is applied in that section—I will take another look for clarification.

Could you expand a bit on how a disconnected slicer works? An example would be really helpful in understanding your approach.

Thanks again,

K

[hnguy71]

Hi @kobkabnaja ,

Yes, of course!

The term disconnected usually refers to a selection table where it's not connected to your normal model via relationships. This gives you additional flexibility to capture user selections:

In the image above, for simplification, this is just a copy of the actual Date table. You would then add the a slicer to either your page or filter pane. Because it is not connected, you are not restricted to the date ranges via relationships:

Then all that's left is the magical dax expression:

Measure = 

VAR _SelectedPeriod = SELECTEDVALUE('DisconnectedSlicer'[YearMonth])                    // Get the selected period

VAR _MinDate = CALCULATE(MIN('DATE'[Date]), 'DATE'[YearMonth] = _SelectedPeriod)        // What's my earliest date in context?
VAR _StartDate = DATE(YEAR(_MinDate), MONTH(_MinDate) - 12, 1)                          // Let's move it down by 12 months
VAR _EndDate = CALCULATE(MAX('DATE'[Date]), 'DATE'[YearMonth] = _SelectedPeriod)        // Return latest date in conetxt

RETURN

CALCULATE(SUM('FACT'[Value]), DATESBETWEEN('DATE'[Date], _StartDate, _EndDate))         // finally return output based on date criteria

This is very advantageous because now you can use it between different pages or visuals making it more flexible and re-usable.