Dynamic time interval for one visual

[KrisKolady]

I have multiple visuals on a page all being filtered by a Date[Year] slicer.

I'd like to have one of the visuals show three years data which dynamically shifts based on the Year selected. For example if the slicer shows 2024 --- the visual shows data 2022 - 2024.

I tried using this measure as a filter for the specified visual, but it does not work:

Display 3 Years =

VAR SelYear = SELECTEDVALUE( DateCalendar[Year] ) // This works if this value is hard coded or it's a disconnected date table slicer

VAR LastPeriodDate = DATE( SelYear, 12, 31 )

VAR StartPeriodDate = DATE( SelYear -2, 1, 1)

VAR CurrDate = MAX( DateCalendar[Date] )

VAR Period = IF( CurrDate <= LastPeriodDate && CurrDate > StartPeriodDate, 1, 0 )

RETURN Period

This does not seem to work. It only works if it's a disconnected Date table that is used in the slicer. I just wanted to make sure that's the only way to do this because using a disconnected date table slicer would mean that I need to change every other visual on the page also.

Thanks for the feedback.

Kris

[Anonymous]

Hi @KrisKolady ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

HI @KrisKolady,

In fact, the relationship and the filter effects from related fields have higher priority than measure filters.
Their effect will be enabled on the virtual table that visual and measure used before the customize measure filter applied and you can't use measure expression and dax funcitons to ignore these effects.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[powerbiexpert22]

Hi @KrisKolady ,

please share sample pbix file or data in excel

you can use below logic to restrict data for only 3 years by creating measure as shown below for that visual

calculate (measure,

                 filter(all(calendar[date]),

                   calendar[date]>=selected date- 3years &&

                   calendar[date]<=selected date)

                )

[lbendlin]

It's the only way.

[KrisKolady]

Thank you for confirming. - Kris