Calculate SUM IF if NOT Blank

[sbarker_11]

Hi, im trying to sum the total approved amount with a loan status of "cancelled" ONLY IF the approved date is blank.

Ive tried this measure, but it isnt giving me the correct number:

CancPost-Approval (£) = CALCULATE(sum(DATA[Approved Amount (sec/Unsec)]),DATA[Approved Date]<>BLANK(),DATA[Loan Status Group]="Cancelled")

its possible that my approved amount calc column is incorrect (it should give me offfer 1 amout and offer 2 if nothing in offer 1?:

Approved Amount (sec/Unsec) = COALESCE(DATA[Unsecured Offer 1 Amount],DATA[Secured Offer 1 Amount])

[Anonymous]

I'm thinking that BLANK never equals null--maybe you need to change Data[Approved Date] <>BLANK()

to

IF NOT ISNULL(Data[Approved Date])

--Nate

[sbarker_11]

Hi it doesnt seem to work, change to this:

CancPost-Approval (£) = CALCULATE(sum(DATA[Approved Amount (sec/Unsec)]), IF NOT ISNULL(Data[Approved Date]),DATA[Loan Status Group]="Cancelled")

returned below error

The syntax for 'NOT' is incorrect. (DAX(CALCULATE(sum(DATA[Approved Amount (sec/Unsec)]), IF NOT ISNULL(Data[Approved Date]),DATA[Loan Status Group]="Cancelled"))).

[Anonymous]

Sorry, got my syntax mixed up--try NOT IS NULL instead of NOT ISNULL.

---Nate

[sbarker_11]

still no luck sadly 😞

The syntax for 'NOT' is incorrect. (DAX(CALCULATE(sum(DATA[Approved Amount (sec/Unsec)]), IF NOT IS NULL(Data[Approved Date]),DATA[Loan Status Group]="Cancelled"))).

[Anonymous]

Hi @sbarker_11 ,

I think what Nate suggests is NOT ISBLANK()
ISBLANK function (DAX) - DAX | Microsoft Learn

Best Regards,
Gao
Community Support Team