Forum Discussion

DBC01's avatar
DBC01
Regular Visitor
1 year ago
Solved

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

  • 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
    )
    • DBC01's avatar
      DBC01
      Regular Visitor

      Thank you Kedar for this, it works exactly as I'd hoped! 🙂