How to give default filters for 2 years , Dynamic filter of 2 years ?

[ramshoney1225]

Hi 

I have a requirment i have 10 years of data 2012 to 2021 data but my requreiment is my year filter should be dynamic to last two years. 

Now it should be default to 2020 and 2021

after getting 2022 year, my default filter should to 2021 and 2022

How to do this dynamically for filtering the page for two last years dynamically?

Thanks,

Ramss

[v-xiaotang · Accepted solution]

Hi @ramshoney1225 

Supposing you have a date table and a fact table,

then create the measure and put the measure into Filters 

Measure = 
    var _Isfiltered=ISFILTERED(dateTable[Year])
    var _maxYearinData=YEAR(CALCULATE(MAX(factTable[Date]),ALL(factTable)))
    var _selectedYear=MIN(dateTable[Year])
return 
    IF(_Isfiltered,IF(YEAR(MIN(factTable[Date]))<=_selectedYear&&YEAR(MIN(factTable[Date]))>=_selectedYear-1,1,0),IF(YEAR(MIN(factTable[Date]))<=_maxYearinData&&YEAR(MIN(factTable[Date]))>=_maxYearinData-1,1,0))

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ramshoney1225 , You need to create this type of column in your year/date table and use that

if([Year] >= year(Today())-1 && [Year] <= year(Today()) , "Last two year" , [Year] )

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=35