Forum Discussion
Anonymous
6 years agoNot applicable
IF formula (or switch) with ranges
I need to assign some categories to various CRM opportunities based on how far out the opportunity is. Not sure how to work with ranges of days (not necessarily date ranges) in an IF statemen...
edhans
Community Champion
6 years agoNotice how the formula works:
Time Passed =
SWITCH(
TRUE(),
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 90, "Older than 90",
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 60, "Older than 60",
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 30, "Older than 30",
FALSE()
)
If it is > 90 days, then do Older than 90.
If it is > 60 days, then do Older than 60.
Notice I didn't have to tell it >60 and <90. I don't need that, because if it is >90, it never gets to condition #2.
So you could modify it this way for your other conditions:
Time Passed =
SWITCH(
TRUE(),
MAX(SomeTable[Probability]) = 80, "30 Days",
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 90, "Older than 90",
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 60, "Older than 60",
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 30, "Older than 30",
DATEDIFF(MAX('Random Date'[Date]),TODAY(),DAY)> 15, "30 Days",
FALSE()
)
SWITCH just keeps going through steps until it finds a it. If Probability = 80, it returns "30 days" and skips all other comparisons.
Anonymous
6 years agoNot applicable
edhans Great, thanks Ed. I think I've got my mind around it now. Thanks for the explanation and clarifications.
- edhans6 years ago
Community Champion
Great Anonymous . Glad to help.
If you want to dig deeper into SWITCH with the TRUE statement and how it works, this is a great article.