Custom Quarter over Quarter

Sagittariu5

New member
Joined
Sep 22, 2020
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,


I’m trying to create a Quarter over Quarter measure, but been unsuccessful in my attempts.
Please note the Period table doesn't have any dates, since the Calendar is a combination of a Ravinia and Julia Calendar.

PrevQtr = CALCULATE(SUM([BONUS]),FILTER(Period,Period[Quarter]<=EARLIER(Period[Quarter])))
QoQ = =DIVIDE(SUM([BONUS]), [PrevQtr]) - 1

Period Table
QuarterYear
Qtr 12019
Qtr 22019
Qtr 32019
Qtr 42019
Qtr 12020
Qtr 22020
Qtr 32020
Qtr 42020
Qtr 12021

Background, the Calendar is a combination of a Ravinia and Julia Calendar starting from 12/10/2018, which has custom Month (4 Weeks), Quarters (13/14 weeks) and Year (52 Weeks) until 2020/1/1 where the Julian Calendar would be being.
 
Found a solution, via this link https://stackoverflow.com/questions/57757128/qoq-measures-in-power-bi

Create a grouped index column (calculated column), which creates a groups for each Year - quarter and then sorted them alphabetically adding an index number.
Index =RANKX(SUMMARIZE(Period,Period[Qtr Year]),Period[Qtr Year],,ASC)
So then correct measure PrevQtr = CALCULATE(SUM([BONUS]),FILTER(ALLSELECTED(Period),Period[Index]=MAX(Period[Index])-1))
and QoQ =DIVIDE(SUM([BONUS]), [PrevQtr])
 
Back
Top