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 an issue with a straight forward time calculation.
I have a table with a start and end time then a calculated column in power query to deduct end time from start time.
I am trying to display this as a time duration in a visual but it seems to default to decimal.
Ideally I would have a dax measure to do this rather than a calculated column. Can somone have a look at my file please to see if there is a solution?
Solved! Go to Solution.
Hi, @Trebor84
There are many ways to get the duration to be displayed, such as subtracting the time in a power query, and then changing the data type to text.
After my attempts, it is difficult to display the time difference as duration in the cluster bar chart visual,
unless it is in text format, but the text format does not compare the value size correctly.
A measure cannot be a value field in a cluster bar chart. So a column may be needed
One way that could work would be to make the duration in hour units.
Hours = Duration *24
Sample:
Result:
If you still insist on using the duration format, it's a good idea to change the visual you're trying to display.(Like table, Multi-row card…)
Hope i didn't make the problem more complicated.
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Trebor84
I'm sorry I can't download the file you uploaded, it shows an alert of an internet error, actually my internet is working fine
As you can see from your attached picture, your Hours column is multiplied by 60, actually it only needs to be multiplied by 24 as @mahoneypat explained, I think that's why your result is different from mine
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Trebor84
There are many ways to get the duration to be displayed, such as subtracting the time in a power query, and then changing the data type to text.
After my attempts, it is difficult to display the time difference as duration in the cluster bar chart visual,
unless it is in text format, but the text format does not compare the value size correctly.
A measure cannot be a value field in a cluster bar chart. So a column may be needed
One way that could work would be to make the duration in hour units.
Hours = Duration *24
Sample:
Result:
If you still insist on using the duration format, it's a good idea to change the visual you're trying to display.(Like table, Multi-row card…)
Hope i didn't make the problem more complicated.
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think the Hours = Duration *24 is the way I would like to go with this. Can you take another look at my file please. I must be missing something really obvious with this.
https://filebin.net/sfbegwwtz4dh40pt/
Your picture shows you are multiplying by 60 when in your text you stated *24. The decimal form of duration is in days so *24 is correct.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
[end time] - [start time] will give you diff in duration in a column but will not add up
You can get in seconds
diff= datediff([start date],[end date], second) //new column
measure =
var Seconds1 = sum(Table[Diff])
return
quotient(Seconds1,3600 ) & ":" & FORMAT ( quotient(mod(Seconds1,3600 ),60), "00" ) & ":" & FORMAT ( mod(mod(Seconds1,3600 ),60), "00" )
also check for Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |