CODEWITHSUNDEEP
powerbidax
Ali Kate Flesch
Ali Kate Flesch

Reputation: 73

Power BI: Quarter over Quarter %Change

To start, here is the Power BI I am working with:

Power BI Table

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:

%Change Quarter over Quarter Attempt

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:

Correct Structure

As a final note, I do have a date table that looks like this:

Date Table

THANK YOU!

Upvotes: 4

Views: 8767

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

[Total Cost by Quarter] should be as simple as SUM(CR[Cost]) if 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.

Also take a look at this similar question: Power BI: Percent Change Formula

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

Upvotes: 3

Related Questions