Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 columns of dates and wish to get the days between these dates. Easy done with a datediff.
However sometimes the to date will be 31/12/2099 as these are dates in a job so that means they are currently in the job. So In this case I would want to use todays date.
I have tried the following:
DaysAtPos = DATEDIFF ([Date from].[Date] , IF ([Date to].[date] = 12/31/2099,NOW(),[Date to].[Date]), DAY)
This gets no errors but simply does not work, I end up with people being in positions for zillions of days which is clearly not right.
If I change the If statement to try and detect IF([Date to].[YEAR] = 2099 nd I get an error saying start dates can't be greater than end dates.
I also tried saying IF([Date to].[date] > NOW() then use NOW() and I get an error saying start dates can't be greater than end dates.
I have tried changing these date fields to all kinds of date formats and it's driving me nuts!
Solved! Go to Solution.
Hi @Mo-PHAT
Try this
DaysAtPos = IF ([Date to].[date] = 12/31/2099, DATEDIFF ([Date from].[Date] , TODAY(), DAY) ,
DATEDIFF([Date from].[Date],[Date to].[Date],DAY) )
If this works foryou please accept this as solution and also give KUDOS.
Cheers
CheenuSing
I'm writing this and getting a scalar error and wondering if there is another way to do this. Please advise, thanks!
Hi @Mo-PHAT
Try this
DaysAtPos = IF ([Date to].[date] = 12/31/2099, DATEDIFF ([Date from].[Date] , TODAY(), DAY) ,
DATEDIFF([Date from].[Date],[Date to].[Date],DAY) )
If this works foryou please accept this as solution and also give KUDOS.
Cheers
CheenuSing
Thank you SO much! I did have to change it slightly to look at the to.year = 2099 but the format worked!! CHAMP
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |