Forum Discussion
POWER BI DAX
I'm trying to get the remaining years with which a driver's license will expire, i'm making use of the Driver's License Expiry date column in my dataset , I want the column to show negative for the Drivers license that have expired before Today's date. Here is my DAX. Expiry Year =DATEDIFF(MIN('Employee'[Drivers_License_Expiry_Date], 'Employee'[Today's Date]), MAX('Employee'[Today's Date], 'Employee'[Drivers_License_Expiry_Date]), YEAR)
Hi Syndicate_Admin ,
You could add an if statement to check if expiry date is less than or greater than today's date, and display the result accordingly.Expiry Year = var _expiryYr = DATEDIFF(MIN('Employee'[Drivers_License_Expiry_Date], 'Employee'[Today's Date]), MAX('Employee'[Today's Date], 'Employee'[Drivers_License_Expiry_Date]), YEAR) RETURN IF ('Employee'[Today's Date] <= 'Employee'[Drivers_License_Expiry_Date], _expiryYr, -1 * _expiryYr)Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂You can also Concatenate 'YRS' with it ,so it can display the number with 'YRS' afterwards.
Expiry Year = var _expiryYrs = DATEDIFF(MIN('Employee'[Drivers_License_Expiry_Date], 'Employee'[Today's Date]), MAX('Employee'[Today's Date], 'Employee'[Drivers_License_Expiry_Date]), YEAR) RETURN IF ('Employee'[Today's Date] <= 'Employee'[Drivers_License_Expiry_Date], _expiryYrs & " Yrs", -1 * _expiryYrs & " Yrs")
6 Replies
Syndicate_Admin Maybe:
Expiry Year = VAR __YearToday = YEAR(TODAY()) VAR __Year = YEAR(MIN('Employee'[Drivers_License_Expiry_Date])) VAR __Result = __Year - __YearToday RETURN __ResultIt is not working @Syndicate_Admin
Hi Syndicate_Admin ,
You could add an if statement to check if expiry date is less than or greater than today's date, and display the result accordingly.Expiry Year = var _expiryYr = DATEDIFF(MIN('Employee'[Drivers_License_Expiry_Date], 'Employee'[Today's Date]), MAX('Employee'[Today's Date], 'Employee'[Drivers_License_Expiry_Date]), YEAR) RETURN IF ('Employee'[Today's Date] <= 'Employee'[Drivers_License_Expiry_Date], _expiryYr, -1 * _expiryYr)Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂It worked perfectly
Thanks Syndicate_Admin
Please mark my answer as the solution to help others as well.
Really appreciate your kudos!
Kind regards,
Rohit
You can also Concatenate 'YRS' with it ,so it can display the number with 'YRS' afterwards.
Expiry Year = var _expiryYrs = DATEDIFF(MIN('Employee'[Drivers_License_Expiry_Date], 'Employee'[Today's Date]), MAX('Employee'[Today's Date], 'Employee'[Drivers_License_Expiry_Date]), YEAR) RETURN IF ('Employee'[Today's Date] <= 'Employee'[Drivers_License_Expiry_Date], _expiryYrs & " Yrs", -1 * _expiryYrs & " Yrs")
