DAX Formula IF Statement

[mcornfield]

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

[v-zhangti · Accepted solution]

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.

[ebeery]

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

[mcornfield]

Thanks for your reply i added a Screenshot to the post

[ebeery]

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

[mcornfield]

I am trying to calculate Sum of Closed Accounts by Month.

Days from Last Invoice= VALUE(Today()-[9. Lastnonblank])

Now I need to get a SUM by Month-Year

I keep getting TOTAL for some reason