Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Asmoday1507
Frequent Visitor

Error in dax formula with counting sales growth with negative values

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

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.