Forum Discussion

vaidehi31's avatar
vaidehi31
Frequent Visitor
5 years ago
Solved

Last N Fiscal Quarters Slicer/Filter

Hello,   I need to create a slicer where I need Last 4 quarters, Last 8 quarters and Last 12 quarters as the items. I have a calendar table and also have the fiscal quarters. The problem I'm facing...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi vaidehi31

     

    You can create a new table as:

    And then use switch to filter different time periods.

    Create a measure like:

    = SWITCH([slicer table], Last 4 quarter, DATESBETWEEN(Last 4 quarter), 
                         Last 8 quarter, DATESBETWEEN(Last 8 quarter),
                         Last 12 quarter, DATESBETWEEN(Last 12 quarter),
        )

     

    You can refer: https://docs.microsoft.com/en-us/dax/switch-function-dax

     

    Best Regards,

    Link

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

vaidehi31 , In case you need rolling data you can try like

 

Rolling 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,QUARTER))

 

 

WIth help from Qtr rank column in date table

Columns

Qtr Year = year([Date]) & [Qtr]
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Year ],,ASC,Dense)

 

Last 4 Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-4 && 'Date'[Qtr Rank]<=max('Date'[Qtr Rank]) ))

 

Or refer my video, if you want to select a date then want to have 4 qtrs display on trend

https://www.youtube.com/watch?v=44fGGmg9fHI

 

 

  • vaidehi31's avatar
    vaidehi31
    Frequent Visitor
    5 years ago

    Hi Amit, thank you for your response.

     

    However, I do not want to calculate sum of sales or anything, just want it in the slicers so that whenever that option is chosen, everything else on the report page filters accordingly. For example, if I choose Last 4 quarters option, all my charts should reflect data only for the last 4 quarters ie. 2020Q4, 2020Q3, 2020Q2, 2020Q1.

     

    Thanks!