Last N Fiscal Quarters Slicer/Filter

[vaidehi31]

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!

[v-xulin-mstf · Accepted solution]

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]

@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]

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!