Forum Discussion
DBC01
1 year agoRegular Visitor
Switch value between dates
Hi All,
I currently have the column Dax script in a custom column. Depending on the date within the 'Service_expiry_date' column it will return the status of the service ("expired", "expiring within 5 months", "test not due").
Cert status =
VAR _today = TODAY()
VAR _date = EDATE(_today, 1) - 1
RETURN
SWITCH(
TRUE(),
[service_expiry_date] <= _today, "Test expired",
[service_expiry_date] <= _date, "Test Expiring within 1 month",
[service_expiry_date] > _date, "Test not yet due",
"Check data" -- This is a catch-all for any unexpected results
)
I would like to add a 4th return value, "Expired within last 5 days". I have tried the below but that produced an error. Does anyone have any ideas as to how this could be achieved?
Cert status =
VAR _today = TODAY()
VAR _today5 = TODAY()-1 || -2 || -3 || -4 || -5
VAR _date = EDATE(_today, 1) - 1
RETURN
SWITCH(
TRUE(),
[service_expiry_date] <= _today5, "Test expired within last 5 days",
[service_expiry_date] <= _today, "Test expired",
[service_expiry_date] <= _date, "Test Expiring within 1 month",
[service_expiry_date] > _date, "Test not yet due",
"Check data" -- This is a catch-all for any unexpected result
)
Thank you in advance 🙂
DBC01
You can try:Cert status =
VAR _today = TODAY()
VAR _fiveDaysAgo = _today - 5
VAR _date = EDATE(_today, 1) - 1
RETURN
SWITCH(
TRUE(),
[service_expiry_date] > _fiveDaysAgo && [service_expiry_date] <= _today, "Test expired within last 5 days",
[service_expiry_date] <= _fiveDaysAgo, "Test expired",
[service_expiry_date] <= _date, "Test Expiring within 1 month",
[service_expiry_date] > _date, "Test not yet due",
"Check data" -- This is a catch-all for any unexpected results
)
2 Replies
- Kedar_PandeSuper User
DBC01
You can try:Cert status =
VAR _today = TODAY()
VAR _fiveDaysAgo = _today - 5
VAR _date = EDATE(_today, 1) - 1
RETURN
SWITCH(
TRUE(),
[service_expiry_date] > _fiveDaysAgo && [service_expiry_date] <= _today, "Test expired within last 5 days",
[service_expiry_date] <= _fiveDaysAgo, "Test expired",
[service_expiry_date] <= _date, "Test Expiring within 1 month",
[service_expiry_date] > _date, "Test not yet due",
"Check data" -- This is a catch-all for any unexpected results
)- DBC01Regular Visitor
Thank you Kedar for this, it works exactly as I'd hoped! 🙂