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 ...
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.

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