Forum Discussion
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 J...
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
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.