Fixing Delta % once and for all

Especially when it comes to financial data, we often need to calculate the percentage change of two values.

Incorrect example calculation

YTD % change = YTD current year / YTD last year - 1

Now this seems quite easy (if we are a beginner and the data is straight forward)

If we build a measure like in my formula above, we will make several mistakes though.

Everyone of you who has more experience in financial modeling or data analysis knows the problems that will come to light sooner or later.

Problem 1:

If our denominator is 0 we will get into trouble, always use the DIVIDE() function!

Problem 2:

If one of our values is negative we will get pretty ambiguous results. Using DIVIDE() will not help us here either.

Take a look at this:

Problem 3:

If we divide by 0, the DIVIDE() function will return BLANK() which it also should do, but if we then subtract 1 we will get -100.0% as a result which is obviously wrong.

So what are we going to do about all these nasty possibilities?

My advice is to talk with your client.

There are multiple solutions of course.

Solution 1: You can agree on a fixed Delta % for negative values (e.g. 10/-5 = XXX % just like 100.000/-1 will be this XXX%)

You can also convert any negative value to e.g. +1 for this percentage calculation only.

If you do so you might get huge % changes though (10.000.000% e.g.)

I am not a big fan of this approach but in some scenarios it might be viable.

Solution #2:

Leave it BLANK() as soon as either the Numerator or Denominator is negative but not if both are.

If both values are negative, we have to use some custom logic to get to a sensible outcome e.g.

-5 / -10 will be +100%

-10 / -5 will be -100%

This way of calculating is definitely open to discussion, I am using it however.

Solution #3

Apply my final DAX code and give me feedback ;)

What I do here is the following:

-If any value is BLANK, it will return BLANK()

-If both values are negative it will use my custom logic (see solution #2) to calculate the percentage change

-If only one of my values is negative, it will return BLANK() as well

Otherwise, it is calculating the percentage change

Delta_% = 
VAR CurrentPeriod = XXX           // Put in your Measure for the current period here
VAR PreviousPeriod = XXX          // Put in your Measure for the previous period here

VAR FinalCalculation_pos = DIVIDE( CurrentPeriod - PreviousPeriod , PreviousPeriod , BLANK() )
VAR FinalCalculation_neg = IF( CurrentPeriod > PreviousPeriod, DIVIDE( PreviousPeriod , CurrentPeriod , BLANK() ) -1 , DIVIDE( CurrentPeriod * -1 , PreviousPeriod , BLANK() ) + 1 ) // Now this seems strange at first sight but what we do here is we output -10 / -5 as -100% and similarly -5 / -10 as +100%

VAR Result = 
SWITCH( TRUE(),
    ISBLANK( CurrentPeriod ) || ISBLANK( PreviousPeriod ) , BLANK() ,   // If one of our values is BLANK, return BLANK
    PreviousPeriod < 0 && CurrentPeriod < 0, FinalCalculation_neg ,     // If both our values are negative, we use custom logic as outlined above
    PreviousPeriod < 0 , BLANK() ,                                      // If we have a negative value in the previous period, return blank since the percentage is ambiguous
    CurrentPeriod < 0 , BLANK() ,                                       // If we have a negative value in the current period, return blank since the percentage is ambiguous
FinalCalculation_pos                                                    // ELSE do our division for positive values
)
Return Result

If you copy this code 1:1 into Power BI, it will look better than here.

A complex calculation with many conditions has some obvious downsides and might slow down your report.

If you need to be ready for any scenario however, give it a try.