Error in dax formula with counting sales growth with negative values

Asmoday1507 — Sun, 06 Feb 2022 19:55:00 GMT

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

Re: Error in dax formula with counting sales growth with negative values

OwenAuger — Mon, 07 Feb 2022 02:11:59 GMT

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

Re: Error in dax formula with counting sales growth with negative values

wdx223_Daniel — Mon, 07 Feb 2022 04:04:52 GMT

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

topic Error in dax formula with counting sales growth with negative values in DAX Commands and Tips

Error in dax formula with counting sales growth with negative values

Re: Error in dax formula with counting sales growth with negative values

Re: Error in dax formula with counting sales growth with negative values