Forum Discussion

brockry1's avatar
brockry1
Icon for Helper II rankHelper II
1 year ago
Solved

Growth % DAX Command

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...
  • brockry1's avatar
    brockry1
    1 year ago
    Growth % =
    IF(
            OR(
                [Prior Year] > 0,
                ISBLANK([Prior Year])
            ),
     
            DIVIDE(
                [Current Year] - [Prior Year],
                [Prior Year],
                1
            )
        )
     
Selva-Salimi's avatar
Selva-Salimi
Icon for Solution Sage rankSolution Sage
1 year ago

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's avatar
brockry1
Icon for Helper II rankHelper II
1 year ago

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:

 

 

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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