simple multi if statement

[Dellis81]

Hello

Within the calendar table, am wanting to add a column with following conditions.  Assume today is 5/7/2020

Prior to or equal to today = "valid"

Prior to current EOM - in this case 5/31/20 - return "EOM"

Dates >current EOM or>= 6/1/20 return "Future"

As you can see in screenshot below, the first if statement is properly evaluated <= today() = "Valid", the else part of the second if statement should be returning "Future"

DateTrue =
IF (
    'CALENDAR'[Date] <= TODAY (),
    "Valid",
    IF ( 'CALENDAR'[Date] <= EOMONTH ( 'CALENDAR'[Date], 0 ), "EOM", "Future" )
)

I have tried various flavors of the EOM, EndOfMonth, and EOMonth functions - with mostly same result.

Always appreciate the help!   Please disregard the "red" question mark,  I was thinking today was May 8th, not 7th.

[harshnathani]

Hi @Dellis81 ,

You can use the Switch () function

DateCalc =

SWITCH(

True(),

'Calendar'[Date] < TODAY(), "Valid",

'Calendar'[Date] >=Today() && 'Calendar'[Date] <= EOMONTH(TODAY(),0), "EOM",

"Future"

)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Dellis81]

Thank you for your quick reply - I did attempt the switch function - but have never completely understood the true componet,  This will help me!  thanks!

[harshnathani]

@Dellis81 ,

Please see this video to understand SWITCH() function better.

https://www.youtube.com/watch?v=-ykkaAtlCMc

Regards,

Harsh Nathani

[kentyler]

A calculated column is calculated only when the data is loaded. So, unless you plan to always load the data each morning, the logic will not hold for future days.

The second issue is that  these 2 categories overlap

Prior to or equal to today = "valid"

Prior to current EOM - in this case 5/31/20 - return "EOM"
5/6 is both prior to or equal to today

and

prior to current EOM

for this condition Dates >current EOM or>= 6/1/20 return "Future"

you don't need to test after the or... dates that are greater than EOM and by definiton  >= the first day of the next month

I've made some assumptions about what you really want and written this measure

Date Category =

var test_date = date(2020,1,14)

VAR current_date = selectedvalue('Calendar'[date])

VAR end_of_month = EOMONTH(test_date,0)

VAR result = if(current_date <= test_date,"valid",if(current_date > test_date , if(current_date > end_of_month,"Future","EOM")))

return result

[camargos88 · Accepted solution]

Hi @Dellis81 ,

Try this one:

DateTrue =

IF (

'Table'[Date] <= TODAY ();

"Valid";

IF ('Table'[Date] <= EOMONTH(TODAY(); 0); "EOM"; "Future" )

)

Ricardo

[Dellis81]

Perfect - thank you!   So simple, once somebody else shows me...