How to calculate from today's date but with variables

[hawkeye159]

Hi all,

I'm trying to write an expression that looks at a target date and will return answers based on the following:

- "overdue" (where target date is before today)

- "due in next 7 days" (where target date is between 0-7 days from today)

- "due 8-14 Days" (where target date is today + 8-14 days)

- "due 15-21 Days" (where target date is today + 15-21 days)

- "out for consultation" (where target date is today + 22 days)

I tried the following:

Testing Timeline = IF(DCAPPL[DETERMINATION DATE]<TODAY(),"Overdue", IF(DCAPPL[DETERMINATION DATE]>=TODAY()+7,"Due in Next 7 Days", "7+ Days"))

But it didn't quite work...

The "overdue" part worked but the "Due in Next 7 Days" and "7+ Days" are the wrong way around... I think!

Anyone out there able to help please?

Many thanks in advance,

Emma

[dharmendars007 · Accepted solution]

Hello @hawkeye159 , 

Using Switch function will in power bi is more readable and easy to understand, I have re-written a dummy measure using switch instead of IF , kindly check if this works and try to replace this columns names in measure to your column names.

Testing Timeline =
SWITCH(
TRUE(),
[DETERMINATION DATE] < TODAY(), "Overdue",
AND([DETERMINATION DATE] >= TODAY(), [DETERMINATION DATE] <= TODAY() + 7), "Due in Next 7 Days",
AND([DETERMINATION DATE] > TODAY() + 7, [DETERMINATION DATE] <= TODAY() + 14), "Due 8-14 Days",
AND([DETERMINATION DATE] > TODAY() + 14, [DETERMINATION DATE] <= TODAY() + 21), "Due 15-21 Days",
[DETERMINATION DATE] >= TODAY() + 22, "Out for Consultation",
"Not Classified")

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[hawkeye159]

Thank you so much!  That's worked perfectly and I can see how it's been written.  I had tried using the SWITCH function but am clearly struggling with how to set out the measure.  Thanks again, it's really appreciated!