Forum Discussion

mcornfield's avatar
mcornfield
Helper III
4 years ago
Solved

DAX Formula IF Statement

I am trying to calculate Sum of Closed Accounts by Month.
I have a column of Dates for the last invoice date for all our customers.
I Created 2 Measures
9. Lastnonblank = CALCULATE(MAX(Dates[Date]),LASTNONBLANK(Dates[Date],[Invoice Count]))
Days from Last Invoice= FORMAT(Today()-[9. Lastnonblank],"#,##0")
Now I need to create a measure that IF Days from Last Invoice id greater then 90 then 1 otherwise 0
I keep getting an error for some reason
 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, mcornfield 

     

    First, you need to check the format of the "IF Test" field. Text cannot be compared with the value 90.

    Correct results can be obtained by modifying the format of the "IF Test" field.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    mcornfield how about showing us the IF statement you attempted to write, and the error you received?


    • mcornfield's avatar
      mcornfield
      Helper III
      4 years ago

      Thanks for your reply i added a Screenshot to the post

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        mcornfield if you would click the "See details" it would tell you exactly what the problem is:

        By using the "FORMAT" function in your [IF TEST] measure, you are converting that date difference into a Text data type.  But then in [IF TEST 2] you are comparing that against an integer value (90), which is not allowed.

        One solution would be to change your [IF TEST] measure to:

         

        IF TEST = VALUE(Today()-[9. Lastnonblank])

         



  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, mcornfield 

     

    First, you need to check the format of the "IF Test" field. Text cannot be compared with the value 90.

    Correct results can be obtained by modifying the format of the "IF Test" field.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.