Over Due Review Calculation

[sokatenaj]

Good morning!

As I am getting acclimated to the calculations of DAX, I am trying to do a calculation of an overdue review. 

I have a series of review dates in a column. If the review is 30 days late, meaning 30 days over the Next Review Date as of TODAY then we want to notate it as Over Due, otherwise, Not Yet Due. I tried doing this expression, but I am stuck:

Over Due= SWITCH (
TRUE (),
Today() >= DATE (( DAY ( Sheet1[Next Review Date] ) + 30), MONTH ( Sheet1[Next Review Date] ) + 30), DAY ( Sheet1[Next Review Date] ) + 30), "Over Due", "Not Yet Due" ))

I think I am close, but I have been banging my head on this for 2 hours. Any help would be much appreciated. Please and thank you!

What am I doing wrong? 

[Anonymous]

Can you please clarify if you are trying to write a calculated column vs a measure?  

The way you have it written, I am going to guess calc column, and it looks like you are adding 30 to ... MONTH and YEAR !?

Over Due= SWITCH (
TRUE (),
Today() >= DATE (( DAY ( Sheet1[Next Review Date] ) + 30), MONTH ( Sheet1[Next Review Date] ) + 30), DAY ( Sheet1[Next Review Date] ) + 30), "Over Due", "Not Yet Due" ))

If you just remove the RED does it do what you want!?

[sokatenaj]

Hi @Anonymous

I am trying to do a calculated column.  So for example, Next Review Date is 1/30/17. Our policy is that any review that is not submitted within 30 days of that date is over due. So I want to say based on TODAY, if Today is greater than next review date plus 30 days, then write "OVER DUE" else "Not Yet Due" as a calculated column. Does that make sense?

I tried removing the critera in red but it did not work. 😞 I appreciate the help!

[Anonymous · Accepted solution]

Let's simplify a bit:

Over Due = IF (TODAY() >= Sheet1[Next Review Date] + 30, "Over Dude", "Not Yet Due")

[sokatenaj]

I got it to work! Your code is correct. I didn't see I had a page level filter. Many thanks!!

[sokatenaj]

Hi @Anonymous,

That formula seems very straight forward, however, it didn't do what I think it was intended. I have a conditional column that says if today is greater than the next review date then "Over Due" and it gives me a value of 543. The formula you gave gives me the same value. The value should be lower. Not sure what's up. Thanks so much for the continued help. 🙂

[Anonymous]

Your Next Review Date column -- is it of type Date!?

[sokatenaj]

Yes indeedy.