Forum Discussion

sbarker_11's avatar
sbarker_11
Icon for Helper I rankHelper I
2 years ago

Calculate SUM IF if NOT Blank

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

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      sbarker_11
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

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

         

        ---Nate