topic Growth % DAX Command in DAX Commands and Tips

Growth % DAX Command

brockry1 — Tue, 03 Dec 2024 17:06:47 GMT

I'm trying to place a conidional DAX formula to show if the prior year is 0 then show 100% growth other wise divide the current year/prior year -1.

When I add the clause "If(Prior Year) isblank,1,

if prior year>0, divide current year/prior year-1

this is giving me a "False" return and not showing the cell as 100%.

Current formula is "

Growth = IF([Prior Year]>0,

(DIVIDE([Current Year]-[Prior Year],[Prior Year])))

and shows this

Need blank cell to show 100%...what am I doing wrong?

Re: Growth % DAX Command

Selva-Salimi — Tue, 03 Dec 2024 17:53:07 GMT

hi brockry1

although you can simply add 1 at the end of your current formula

Growth = IF([Prior Year]>0, (DIVIDE([Current Year]-[Prior Year],[Prior Year])),1)

add +0 at the end of your prior calculation, and define the condition based on zero.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Growth % DAX Command

brockry1 — Tue, 03 Dec 2024 18:34:02 GMT

It solves for the prior year of 0 equaling 100% however it sorts and shows all the addresses as the same and this isn't true: See screen shot:

Re: Growth % DAX Command

FreemanZ — Tue, 03 Dec 2024 21:19:14 GMT

hi brockry1 ,

that might be another issue. can you tell more about your data model, and the code for the two measures, [Current Year] and [Prior Year]?

Re: Growth % DAX Command

Anonymous — Thu, 05 Dec 2024 03:07:33 GMT

Hi brockry1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Current Year = SUM('Table'[Sales])

Prior Year = VAR _year = SELECTEDVALUE ( 'Table'[Year] ) VAR _category = SELECTEDVALUE ( 'Table'[Category] ) RETURN CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = _year - 1 && 'Table'[Category] = _category ) )

Growth% = IF([Prior Year]>0,(DIVIDE([Current Year]-[Prior Year],[Prior Year])),1)

As shown in the screenshot above, I applied the same formula for Growth% as you did, and it returned the expected result based on the sample data. However, it seems you didn't get the expected result. Could you please provide some sample data (excluding any sensitive information) and the details FreemanZ suggested? Thank you.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

Best Regards

Re: Growth % DAX Command

brockry1 — Mon, 09 Dec 2024 16:26:38 GMT

Growth % =

IF(

OR(

[Prior Year] > 0,

ISBLANK([Prior Year])

DIVIDE(

[Current Year] - [Prior Year],

[Prior Year],

)

Re: Growth % DAX Command

brockry1 — Mon, 09 Dec 2024 16:27:00 GMT

This was the formula that worked.