Calculation Help

[PBrainNWH]

The below calculation is returning all "Retained" values, is there something obvious you can see that's making that happen? The second IF statement should be like an ELSEIF, but PBI doesn't like that.

FY25 Status =

IF(

    SUM('Donations by Customer by FY'[2024 Donation Dollars]) <> 0 && SUM('Donations by Customer by FY'[2025 Donation Dollars]) <> 0,

    "Retained",

    IF(

        SUM('Donations by Customer by FY'[2024 Donation Dollars]) = 0 && SUM('Donations by Customer by FY'[2025 Donation Dollars]) <> 0,

        "New425",

        "Lapsed"

    )

)

[gmsamborn · Accepted solution]

Hi @PBrainNWH 

To expand on @aduguid 's measure, for debugging it might be easier like the following.  If you aren't getting the correct result, you can always RETURN either of the variables for debugging.

FY25 Status 1 =
VAR _2024 = SUM( 'Donations by Customer by FY'[2024 Donation Dollars] )
VAR _2025 = SUM( 'Donations by Customer by FY'[2025 Donation Dollars] ) 
VAR _Logic =
	IF(
		_2024 <> 0
			&& _2025 <> 0,
		"Retained",
		IF(
			_2024 = 0
				&& _2025 <> 0,
			"New425",
			IF(
				_2024 <> 0
					&& _2025 = 0,
				"Lapsed",
				BLANK()
			)
		)
	)
RETURN
	_Logic

I hope this helps.

[aduguid]

Try this measure

FY25 Status = 
IF (
    SUM('Donations by Customer by FY'[2024 Donation Dollars]) <> 0 
    && SUM('Donations by Customer by FY'[2025 Donation Dollars]) <> 0,
    "Retained",
    IF (
        SUM('Donations by Customer by FY'[2024 Donation Dollars]) = 0 
        && SUM('Donations by Customer by FY'[2025 Donation Dollars]) <> 0,
        "New425",
        IF (
            SUM('Donations by Customer by FY'[2024 Donation Dollars]) <> 0 
            && SUM('Donations by Customer by FY'[2025 Donation Dollars]) = 0,
            "Lapsed",
            BLANK()  -- This will handle any unexpected cases
        )
    )
)