Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to 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
)
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! 🙂
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
)
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")