Error in dax formula with counting sales growth with negative values

[Asmoday1507]

Hi everyone!

Profit in 2022: 51

Proft in 2021: (-31)

How to calculate growth YoY? 

My actual function is as below:

=DIVIDE(PROFIT 2022, PROFIT 2021,BLANK())

But the result of this calculation is wrong, because it is (-277%), the profit is on the upside, so the result must be positive.

Regards

[wdx223_Daniel]

=DIVIDE(PROFIT 2022-PROFIT 2021, ABS(PROFIT 2021),BLANK())

[OwenAuger · Accepted solution]

Hi @Asmoday1507 

This is really a question of what convention you want to follow when calculating growth rates between profit values when either one is negative. I recommend getting the opinion of the consumers of your report(s) to see what they would expect in such cases.

There is an old Wall Street Journal convention, which states that percentage growth should not be shown for profit values when either the current or prior value is negative.

There is another convention I have seen where the absolute value of the prior value is used in the denominator, however I think this could give misleading results.

Personally, I would recommend returning a blank result if the percentage doesn't make sense due to negative numbers.

In DAX, something like this would achieve this:

=
VAR ProfitCurrent = [Profit Current]
VAR ProfitPrior = [Profit Prior]
RETURN
    IF (
        OR ( ProfitCurrent < 0, ProfitPrior < 0 ),
        DIVIDE ( ProfitCurrent, ProfitPrior ) -- Automatic blank if ProfitPrior = 0
    )

Also, not sure if you want to subtract 1 from the result of DIVIDE to give a growth rate rather than a ratio.

Those are some thoughts anyway.

Regards,

Owen