To start, here is the Power BI I am working with:
I want to calculate the %Change in Cost Quarter over Quarter.
As shown in the table above, I have the Cost Total for Q1, Q2, Q3, and Q4 in the Total Cost by Quarter Column, which I calculated using this formula:
Total Cost By Quarter =
IF (
[Quarters] = "Q1",
CALCULATE (
SUM ( CR_Months_ByMonth[Cost] ),
FILTER ( CR_Months_ByMonth, [Quarters] = "Q1" )
),
IF (
[Quarters] = "Q2",
CALCULATE (
SUM ( CR_Months_ByMonth[Cost] ),
FILTER ( CR_Months_ByMonth, [Quarters] = "Q2" )
),
IF (
[Quarters] = "Q3",
CALCULATE (
SUM ( CR_Months_ByMonth[Cost] ),
FILTER ( CR_Months_ByMonth, [Quarters] = "Q3" )
),
IF (
[Quarters] = "Q4",
CALCULATE (
SUM ( CR_Months_ByMonth[Cost] ),
FILTER ( CR_Months_ByMonth, [Quarters] = "Q4" )
)
)
)
)
)
However, I could not figure out how to calculate %Change between quarters using another Calculated column, due to the repeating values (multiple Q1s, Q2s, etc in [Total Cost By Quarter]).
So, I attempted to calculate the %Change using Measures. I made a measure for the Q1 Cost, Q2 Cost, Q3 Cost, and Q4 Cost, using a formula like the one below:
Q1Sum =
CALCULATE (
SUM ( CR_Months_ByMonth[Cost] ),
FILTER ( CR_Months_ByMonth, [Quarters] = "Q1" )
)
I then made a new measure to calculate Q12%Change, Q23%Change, and Q34%Change, using a formula like the one below:
Q12%Change =
( DIVIDE ( [Q2Sum] - [Q1Sum], [Q1Sum] ) )
* 100
This is the result that I get using the calculated measures:
This structure does not yield good visuals and I am certain there is a simpler, more efficient way to accomplish Quarter over Quarter %Change.
This is my desired result:
As a final note, I do have a date table that looks like this:
THANK YOU!