Forum Discussion
Duration not summing up correctly
Hello,
I am extremely new to PBI, and am trying to support on of my admins in a dashboard calculation for learning hours that is incorrect. Our use case is to sum up total learning hours based on HH:MM:SS duration of training in a CSV export from LMS that is imported to PBI. When in excel and summing up it reflects approx 36k hours but in the PBI measure on the dashboard it only shows 25k. We have exhausted various sites as well as the forum here and have concluded that duration over 24 hours is not tabulating correctly into the total sum of duration. We have tried various code snips but have not been able to get this to reflect correctly in the dashboard. PLEASE HELP!!!!
Current code snippet:
VAR _sum=sum(Dashboard_All_Course_Completion[Duration (HH:MM) for Reporting])*86400
VAR _h=int(_sum/3600)
VAR _m=int((_sum-_h*3600)/60)
VAR _s=int(_sum-_h*3600-_m*60)
return _h.
If anyone is kind enough to respond please keep in ming I have almost zero experience with PBI (Plenty in excel and Power apps and even some coding classes) so treat me like a complete noob 😂
3 Replies
Jtischler , if the time data type is time and you need a sum
Time(0,0,0) + sumx(Table, hour([Datetime]) + minute([Date Time])/60 + second([Date Time])/60 ) /24
or refer
Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.amitchandak The problem seems to be that prior to the summing function, it appears that time over 24 hours is not 'seen' after the import occurs.
pre import excel format (HH:MM:SS):
shows in excel formula bar as DATE/TIME :
When it imports to PBI - The column is FORMATTED to TIME and displays as HH:MM:SS but the time shown is WRONG. As you can see below, it is showing the actual TIME stamp as reflected in the above screenshot vs the duration of 161:48 that it should
Does this help?
Hi Jtischler ,
It is beacause that in Power Query Editor, there are two data types not currently present in Data or Report View: Date/Time/Timezone and Duration.
When a column with these data types is loaded into the model and viewed in Data or Report view, a column with a Date/Time/Timezone data type will be converted into a Date/Time, and a column with a Duration data type is converted into a Decimal Number.
You can try to use FORMAT function or use DURATION in Power Query.
Column = FORMAT('duration'[duration],"hh:mm:ss")Please reference the following document.
FORMAT function (DAX) - DAX | Microsoft Learn
Solved: Re: Excel time format [h]:mm:ss on power BI - Microsoft Power BI Community
Data types in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Community Support Team _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
