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 is the quarters are repeatitive hence unable to either create groups or a column. For example, 2020 Q4 will be present in Last 4 quarters, Last 8 quarters as well as Last 12 quarters item.

 

Any help is highly appreciated.

 

Thanks!

Need Help

3 Replies

  • 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!

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    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