Date IF Statement

[AndrewG12345]

Hi All,

I'm trying many different things but getting myself more confused.. i'm trying to acheive a simple calculated column based upon a date field.

If the date field is greather than today+30, show "Good", if date is less than today +30 show "about to expire" and if date is less than today show "Expired".

Can someone put me out of my misery please 🙂

Many thanks

[AndrewG12345]

Greg,

If i have blanks in the date field can i have them show as "Good" too? i assume an additional if statement?

thanks

Andrew

[amitchandak]

@AndrewG12345 , Create a new column in the table like

Switch ( True(),

[Date]>=today()+30, "Good",

[Date]>=today(), "about to expire",

"Expired"

)

[Greg_Deckler · Accepted solution]

@AndrewG12345 - I'm thinking:

Column =
  SWITCH(TRUE(),
    [Date] < TODAY(),"Expired",
    [Date] > TODAY() + 30,"Good",
    "About to Expire"
  )

BTW, since I see that you are a New Member:

Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[AndrewG12345]

Hi @Greg_Deckler ,

If i have the field with blanka info that i wanted to ignore as it doesnt have an expiry, is that another IF statement in there, if blank "Good" again?

thanks

A

[Greg_Deckler]

@AndrewG12345 - If I understand, yes:

Column =
  SWITCH(TRUE(),
    ISBLANK([Date]),"Good",
    [Date] < TODAY(),"Expired",
    [Date] > TODAY() + 30,"Good",
    "About to Expire"
  )

[AndrewG12345]

Perfect .. thank you.

[AndrewG12345]

Many thanks Greg and noted on the checking first. 🙂