# Power BI: Quarter over Quarter %Change

1 view
in BI

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!

by (47.2k points)
selected

• [Total Cost by Quarter] should be as simple as SUM(CR[Cost]) is placed into a matrix that has quarters on the rows/columns.

• The trickier part is referencing the previous quarter to get the percent change. It will look something like this:

% Change =

VAR PrevQtrCost = CALCULATE(SUM(CR[Cost]), PREVIOUSQUARTER(DateTable[Date]))

RETURN DIVIDE(SUM(CR[Cost]), PrevQtrCost) - 1

• The VAR line might be a bit different depending on how exactly you have your DateTable related to the CR table.

• If you aren't linking on a date, then try something along these lines:

% Change =

VAR PrevQtr = MOD(MAX(DateTable[FiscalQuarterNumber]) - 2, 4) + 1

VAR PrevQtrCost = CALCULATE(SUM(CR[Cost]), DateTable[FiscalQuarterNumber] = PrevQtr)

RETURN DIVIDE(SUM(CR[Cost]), PrevQtrCost) - 1