Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
vaidehi31
Frequent Visitor

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!

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @vaidehi31

 

You can create a new table as:

v-xulin-mstf_0-1618451545801.png

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

View solution in original post

3 REPLIES 3
v-xulin-mstf
Community Support
Community Support

Hi @vaidehi31

 

You can create a new table as:

v-xulin-mstf_0-1618451545801.png

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
Super User
Super User

@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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.