Solved: Create rolling 3 month groups

[bwiley]

I have seen a lot of posts about rolling 90 days but I am looking for something different. I need to present data in a graph format showing sales for the last 3 months, the 3 months before that, etc until I get five 3 month groupings.

Ex. (These are fiscal years, I am not from the future)

Sep-Nov 2022

Dec 2022-Feb 2023

Mar-May 2023

June-Aug 2023

Sep-Nov 2023

Currently I have it working but am using several calculated columns using SWITCH that I have to update each month, such as: 

QuarterRolling = SWITCH( TRUE(),

'Date'[Month Number] =1, "Q1",

'Date'[Month Number] =2, "Q1",

'Date'[Month Number] =3, "Q2",

'Date'[Month Number] =4, "Q2",

'Date'[Month Number] =5, "Q2",

'Date'[Month Number] =6, "Q3",

'Date'[Month Number] =7, "Q3",

'Date'[Month Number] =8, "Q3",

'Date'[Month Number] =9, "Q4",

'Date'[Month Number] =10, "Q4",

'Date'[Month Number] =11, "Q4",

'Date'[Month Number] =12, "Q1",

 "Not Quarter"

)

YearAndQuarterRolling = 'Date'[year] & "-" & 'Date'[QuarterRolling]

LegendandChicletName = SWITCH( TRUE(),

'Date'[YearAndQuarterRolling] = "2022-Q4", "Sept-Nov FY23",

'Date'[YearAndQuarterRolling] = "2022-Q3", "June-Aug FY23",

'Date'[YearAndQuarterRolling] = "2022-Q2", "March-May FY23",

'Date'[YearAndQuarterRolling] = "2022-Q1", "Dec-Feb",

'Date'[YearAndQuarterRolling] = "2021-Q1", "Sept-Nov FY22",

 "Not Quarter"

)

I feel like there must be a formula that can automate this for me?

Thanks!!

[v-rongtiep-msft · Accepted solution]

Hi @bwiley ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 columns.

Column = var _month=MONTH('Table'[Date])
var _1=IF(_month>=3&&_month<=5,"Q2",IF(_month>=6&&_month<=8,"Q3",IF(_month>=9&&_month<=11,"Q4","Q1")))
return _1

Column 2 = var _month=MONTH('Table'[Date])
var _year=YEAR('Table'[Date])

var _23=RIGHT(_year,2)
return
'Table'[Column]&"FY"&_23

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bwiley]

This works thank you! 

One last question. My data is only updated once a month and usually around the 15th of each month. With this solution I am assuming when the month changes over my reporting will be incorrect since there will be no December data on January 1st?

I believe this tweak will allow be to make it correct on January first and then switch back to original solution once I have December data?

Column = var _month=(MONTH('Date'[Date])-1)

var _1=IF(_month>=3&&_month<=5,"Q2",IF(_month>=6&&_month<=8,"Q3",IF(_month>=9&&_month<=11,"Q4","Q1")))

return _1

OR 

Column = var _month=(MONTH('Date'[Date])-0) //current data = 0, data month behind change to -1

var _1=IF(_month>=3&&_month<=5,"Q2",IF(_month>=6&&_month<=8,"Q3",IF(_month>=9&&_month<=11,"Q4","Q1")))

return _1

[FreemanZ]

hi @bwiley 

it seems you are expecting the Time Intelligence Functions, like:

https://dax.guide/datesbetween/

https://dax.guide/datesinperiod/