Forum Discussion

Kirsten10's avatar
Kirsten10
Microsoft Employee
5 years ago

Rolling QoQ Calculation

Hi,

 

I need to create a measure that would show QoQ growth but the quarters would be rolling. So for this current period I a looking to compare Mar-May vs Jun-Aug, and then next month Apr-Jun vs Jul-Sep.

 

I have created a rolling 3 months sum but am not sure how to create one for the 3 months prior.

 

Any suggestions?

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Kirsten10 , You have custom qtr. For this first of all create qtr Start Date.

    I this this FY will end in feb.  have these columns in date table

    Qtr Start Date = 
var _st ='Date'[Start Of Year]
var _Q = QUOTIENT(DATEDIFF([Start Of Year], 'Date'[Date],MONTH),3)*3
return date(year(_st),month(_st)+_Q,1)

//Year other then feb end //like ending in march
Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)

Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

     

    Have these measure for Qtr vs Qtr

    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

     

    Power BI — Qtr on Qtr with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

     

    get any FY -https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

     

    • Kirsten10's avatar
      Kirsten10
      Microsoft Employee
      5 years ago

      Thanks for your help but I'm not sure if that will give me what I need. Your formula sets the end month (at Feb?) and I would need rolling months, so the end month would keep changing.

      Essentially what I am after is to sum the last 3 months of data, which I can do, and then sum the 3 months before that. I'm not sure how to filter my sum correctly so that it only includes the data from 6-4 months ago.