DAX calculate current status based on 2 date columns

[serky]

Hi
I have a data set with 2 date columns (start and end). For each record I need to determine if TODAY is before the period, during the period or after the period. My formula works OK if I'm testing TODAY in either start date or end date but I don't know how to test them together.

My current formula is:

Status:= if(countrows(values(Table[start_date]))=1,
if(values(Table[start_date])<today, "obsolete","ok"),
0)

Do I create a 'nested if' and also test Table[end_date] for 1 value, then incorporate [start_date] and [end_date] into my 'if' statement?

Or is there a better way to do this? (I am intending to use SWITCH to improve the readability).

Thanks</today,>

 

[spiralrain]

yes you will want a nested IF statement.

If today is before start date then:
True = Before
False = If today is after end date then:
True = After
False = During

Hope that helps.

 

[serky]

Hi @spiralrain

thanks for the feedback. I just realised my original formula was incomplete so here it is:

Status:= if(countrows(values(Table[start_date]))=1,
if(values(Table[start_date])>TODAY(), "Before","TBD"),
"")

This isn't what I was asking though (sorry if I wasn't clear) - I was more concerned about whether I have to do a test for the existence of [end date] as well as [start_date] ie - if(countrows(values(Table[start_date]))=1 AND if(countrows(values(Table[end_date]))=1

I just find it bizarre in DAX that we have to test for certain parameters before a calculation will work (unlike Excel where you just get an error; it's not like we test if a cell is a number before adding, for example).

Thanks