Power BI: Quarter over Quarter %Change

Question

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!

Answer 1

• [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

If you are preparing for the Power BI certification exam, then take up this Power BI Microsoft certification Course online by Intellipaat that offers instructor-led training, hands-on projects, and certification.