Forum Discussion

Powerbyosh's avatar
Powerbyosh
Icon for Helper I rankHelper I
3 years ago

5 years rolling data based on slicer (year) selection

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.

"DAX create dynamic table from slicer date"
"DAX"
@Cummilative
@Dax help
DAX

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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