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 all,
I need your help!
I have this situation:
I would like to substract both dates (DATE & TIME and Start Time).
In my mind appear this step: creates a new personalized column doing DATE&TIME - Start time. The result is something like Finish time column.
When I close and apply and take all the data to the desktop, the program isn ot reading well its type.
What should I do?
Thank you so much.
PS: this Finish Time colum will be used to substract with other date.
Note that all the result dates will last less than 24h, so the first number (day) will be 0 (0.07:25:52)
Solved! Go to Solution.
Hi @Anonymous ,
"Duration" type can't be passed to Data view. Since all the result dates will last less than 24h, it is suggested to create a calculated column like below:
Column =
VAR DateDiffSeconds =
DATEDIFF ( [Start Time], [DATE & TIME], SECOND )
VAR Hours =
TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds = DateDiffSeconds - Minutes * 60 - Hours * 3600
VAR Result =
Hours & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
RETURN
CONVERT ( Result, DATETIME )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
"Duration" type can't be passed to Data view. Since all the result dates will last less than 24h, it is suggested to create a calculated column like below:
Column =
VAR DateDiffSeconds =
DATEDIFF ( [Start Time], [DATE & TIME], SECOND )
VAR Hours =
TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds = DateDiffSeconds - Minutes * 60 - Hours * 3600
VAR Result =
Hours & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
RETURN
CONVERT ( Result, DATETIME )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Subtracting two DateTime columns will give you a duration. You likely need to wrap your calculation in one of the Duration functions to get a usable number. For example, Duration.TotalHours([Date & Time] - [Start Time]) would get you the # of hours between them in decimal form.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |