Forum Discussion

bwiley's avatar
bwiley
Frequent Visitor
3 years ago
Solved

Solved: Create rolling 3 month groups

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!!
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      bwiley
      Frequent Visitor
      3 years ago

      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