5 years rolling data based on slicer (year) selection

[Powerbyosh]

Hi All,

I have a requirement that needs to display 5 years of data if selected YTD, 12 months of data if selected MTD, selected month && 4 quarters of data if selected QTD.

 How do I get 5 years of data?

Please drop me your suggestions or answers.

[v-yangliu-msft]

Hi  @Powerbyosh ,

Here are the steps you can follow：

1. Enter – Create a table.

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table2'[Group])
var _today=TODAY()
return
SWITCH(
    TRUE(),
    MAX('Table2'[Group])="YTD" && MAX('Table'[Date]) >= DATE(YEAR(_today)-5,MONTH(_today),DAY(_today))&& MAX('Table'[Date]) <= _today,1,
    MAX('Table2'[Group])="MTD" && MAX('Table'[Date]) >= DATE(YEAR(_today)-1,MONTH(_today),DAY(_today))&& MAX('Table'[Date]) <= _today,1,
    MAX('Table2'[Group])="QTD" && MAX('Table'[Date]) >= DATE(YEAR(_today),MONTH(_today)-4,DAY(_today))&& MAX('Table'[Date]) <= _today,1,0
)

Measure =
SUMX(
    FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])),[rand])

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[Powerbyosh]

Thank you. It worked