DAX formula for calculating growth / decline vs LY for gross margin

[elkjoperik]

Hi!

I have all the data for the gross margin for this year and last year (two different columns). 

The tricky part is the the calculation gives 4 scenarios:

A. Postive / negative = negative, but it's realy a positive growth vs LY

B. Positive / positive = OK to calculate

C. Negative / negative = positive, but in reality it an be a positive or negative result.

D. Negative / positive =  negative 

I'm having a hard time writing a DAX formula that takes these four scenarios into consideration.

This years coulmn name = GM FY

Last years coulmn name = GM LY

[v-yuezhe-msft · Accepted solution]

Hi @elkjoperik，

From my point of view, it is a business logic issue rather than a DAX formula issue. And it is better to use the following formula to calculate gross margin growth/decline in your scenario.

Growth = ( (Table2[GM FY]-Table2[GM LY])/ABS(Table2[GM LY]))*100 & "%"

For mode details, please review the example in the attached PBIX file.

Thanks,
Lydia Zhang

[elkjoperik]

Thanks a lot, solved the issue!

[elkjoperik]

bump, anyone ?