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.
I have two dates columns (Request Date and Event Date). I need to calculate the time between each date, to identify how long clients have waited for their event. However, in our data, if an event (appt) has not yet been made the default date which is returned is 01/01/2099. So, when calculating days between, with a 'normal' date field, I get exactly what I need to see. When I try to calculate days between when the 01/01/2099 is there, I obviously get nonsense days!!
What I need to be able to do is replace all of the instances of 01/01/2099 in the event date column, with today's date (which will obviously change each day with refresh) or leave the event date in place if it's a 'normal date'.
I've tried using IF but the calculated column simply returns 01/01/2099 and therefore doesn't appear to work!? Any help would be greatly received:
Request Date | Event Date | Days Between | Event Date using TODAY's date | Corrected Days Between |
01/11/2022 | 01/01/2099 | 27820 | 17/11/2022 | 16 |
02/11/2022 | 01/01/2099 | 27819 | 17/11/2022 | 15 |
03/11/2022 | 07/11/2022 | 4 | 07/11/2022 | 4 |
04/11/2022 | 15/11/2022 | 11 | 15/11/2022 | 11 |
05/11/2022 | 08/11/2022 | 3 | 08/11/2022 | 3 |
The final two columns is what I need my data to show! Many thanks,
Solved! Go to Solution.
add a calculated column as:
Column =
IF(
TableName[Event Date] = DATE(2099,1,1)
,TODAY() - TableName[Request Date]
,TableName[Event Date] - TableName[Request Date]
)
Proud to be a Super User!
This is a great community forum. Much appreciated!
add a calculated column as:
Column =
IF(
TableName[Event Date] = DATE(2099,1,1)
,TODAY() - TableName[Request Date]
,TableName[Event Date] - TableName[Request Date]
)
Proud to be a Super User!
Many thanks! Works like a charm.
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 |
---|---|
99 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
104 | |
85 | |
73 |