Growth % DAX Command

[brockry1]

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? 

[brockry1 · Accepted solution]

Growth % =

IF(

        OR(

            [Prior Year] > 0,

            ISBLANK([Prior Year])

        ),

        DIVIDE(

            [Current Year] - [Prior Year],

            [Prior Year],

            1

        )

    )

[brockry1]

This was the formula that worked. 

[Selva-Salimi]

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)

 or

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.

[brockry1]

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:

[Anonymous]

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

[FreemanZ]

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]?