Can anyone tell me why my var function is not working?

[DBC01]

Hi Everyone,

New Power BI user here, dropped in at the deep end! I wonder if anyone could help me troubleshoot a custom column that is causing a few issues.  I have an exisiting column that called service_expiry_date, which contains the date a certain test certfication expires. These are saved in Data format. I have put together the below DAX code in a custom column with the hope of returning 3 values based off of todays date:

• Test expiring within 2 months (this will be any test certficate that is due to expire within the 2 months)
• Test expired (any certficates that have already expired)
• Test not yet due (all other certficates that are not expired or expiring within 2 months)

Cert status = 
var _date = DATE(Year(today()),MONTH(today())+2,1)-1
Return
Switch(True(),
[service_expiry_date]<_date &&[service_expiry_date]>= today(), "Test Expiring within 2 months",
[service_expiry_date]<_date &&[service_expiry_date]<= today(), "Test expired",
[service_expiry_date]> _date, "Test not yet due")

Whilst that seems to work well, I am also getting a unexpected 4th result: "(blank)". All of the blank results have the same service expiry date: 31st May.  I would expect these entries to fall under the "Test Expiring within 2 months" as 31st May is within 2 months from now. I would imagine that I have missed a clause somewhere. Could anyone shine a light on where I have gone wrong?

Any help would be gratefully recieved.

[DBC01]

Thank you both @vs_7, @AmiraBedh for your quick responses. I have tried both and experienced the same issue again with vs_7's solution, but AmriaBedh's worked perfectly. Thank you very much! 🙂 

[AmiraBedh · Accepted solution]

I have a doubt regarding the logical tests in your SWITCH statement [service_expiry_date] < _date && [service_expiry_date] >= today() and [service_expiry_date] < _date && [service_expiry_date] <= today() could be clearer.
The first condition should capture all dates up to _date (excluding _date itself), and the second condition checks for dates before today(), which seems redundant since the first condition already includes today().
There's a logical gap here because neither condition explicitly handles the case where [service_expiry_date] == today().

I am simplifying it as :

Cert status =
VAR _today = TODAY()
VAR _date = EDATE(_today, 2) - 1
RETURN
SWITCH(
TRUE(),
[service_expiry_date] < _today, "Test expired",
[service_expiry_date] <= _date, "Test Expiring within 2 months",
[service_expiry_date] > _date, "Test not yet due",
"Check data" -- This is a catch-all for any unexpected result
)

[vs_7]

Hi @DBC01 ,

try below dax measure

Cert status =
VAR _date = DATE(Year(today()), MONTH(today()) + 2, 1) - 1
RETURN
Switch(True(),
[service_expiry_date] <= today(), "Test expired",
[service_expiry_date] < _date, "Test Expiring within 2 months",
[service_expiry_date] > _date, "Test not yet due")