Forum Discussion

KrisKolady's avatar
KrisKolady
Frequent Visitor
1 year ago

Dynamic time interval for one visual

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
Tips and Tricks

5 Replies

  • powerbiexpert22's avatar
    powerbiexpert22
    Impactful Individual
    1 year ago

    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)

                    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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