Forum Discussion
Aggregating and Visualising Duration Data
I have some data showing the number of hours worked but I'm not clear what modelling or formatting I need to do to be able to aggregate it and visualise it in PowerBI.
In power query editor I have 6 columns with the startTime & endTime as type Time and hours as type duration. The duration was not calculated, it was ingested.
Restriction
I have to use a particular version of PBI desktop that doesn't have dynamic formatting
| EmployeeID | Dept | Date | startTime | endTime | hoursWorked |
| abcd | dept1 | 01/01/2020 | 09:00:00 | 20:00:00 | 0.11:00:00 |
| abcd | dept1 | 02/01/2020 | 09:00:00 | 20:15:00 | 0.11:15:00 |
| jkl | dept1 | 01/01/2020 | 17:45:00 | 18:15:00 | 0.00:30:00 |
| jkl | dept2 | 01/03/2020 | 15:00:00 | 15:30:00 | 0.00:30:00 |
I'd like to be able to visualise as charts
- total hoursWorked per employeeID per month
- total hoursWorked per dept per month
My issues are the
- hoursWorked column doesn't support sum (just count or count distinct) in the designer. In Power Query its type is duration, in report designer its data type is Time and there is no duration option.
- If i duplicate the hours and change it to type decimal then I can sum but can't figure out how to
- ensure the result is accurate
- format the result so its user friendly in days, hours mins.
- Not clear what limitations apply or features exist that could help me
What I have tried.
Duplicating the hoursWorked value as a decimal
Creating a Measure SumFormatted = format(SUM('2019 to 2023'[hoursWorked]),"dd \d hh\h:mm:ss") which is returned as text but is returning a different result to the decimal sum assuming the int value represents days (24hours)
Any advice welcome on the power query, measures needed and limitations
Hi mobul ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create calculated columns.
hoursWorked (h) = var _start=CONVERT([Date]&" "&[startTime],DATETIME) var _end=CONVERT([Date]&" "&[endTime],DATETIME) RETURN DATEDIFF(_start,_end,SECOND)/3600Month = MONTH([Date])Year = YEAR([Date])(3) We can create measures.
total hoursWorked per dept per month = CALCULATE ( SUM ( 'Table'[hoursWorked (h)] ), FILTER ( ALLSELECTED ( 'Table' ), [Dept] = MAX ( 'Table'[Dept] ) && [Year] IN VALUES ( 'Table'[Year] ) && [Month] IN VALUES ( 'Table'[Month] ) ) )total hoursWorked per employeeID per month = CALCULATE ( SUM ( 'Table'[hoursWorked (h)] ), FILTER ( ALLSELECTED ( 'Table' ), [EmployeeID] = MAX ( 'Table'[EmployeeID] ) && [Year] IN VALUES ( 'Table'[Year] ) && [Month] IN VALUES ( 'Table'[Month] ) ) )(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi mobul ,
You can create measures.
Format 1 = var _a=[total hoursWorked per dept per month] var _b=ROUNDDOWN(_a,0) var _c=_a-_b return SWITCH(TRUE(), _b=0,_c*60 & "m", _c=0, _b& "h", _b&"h"&(_a-_b)*60&"m")Format 2 = var _a=[total hoursWorked per employeeID per month] var _b=ROUNDDOWN(_a,0) var _c=_a-_b return SWITCH(TRUE(), _b=0,_c*60 & "m", _c=0, _b& "h", _b&"h"&(_a-_b)*60&"m")Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi mobul ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create calculated columns.
hoursWorked (h) = var _start=CONVERT([Date]&" "&[startTime],DATETIME) var _end=CONVERT([Date]&" "&[endTime],DATETIME) RETURN DATEDIFF(_start,_end,SECOND)/3600Month = MONTH([Date])Year = YEAR([Date])(3) We can create measures.
total hoursWorked per dept per month = CALCULATE ( SUM ( 'Table'[hoursWorked (h)] ), FILTER ( ALLSELECTED ( 'Table' ), [Dept] = MAX ( 'Table'[Dept] ) && [Year] IN VALUES ( 'Table'[Year] ) && [Month] IN VALUES ( 'Table'[Month] ) ) )total hoursWorked per employeeID per month = CALCULATE ( SUM ( 'Table'[hoursWorked (h)] ), FILTER ( ALLSELECTED ( 'Table' ), [EmployeeID] = MAX ( 'Table'[EmployeeID] ) && [Year] IN VALUES ( 'Table'[Year] ) && [Month] IN VALUES ( 'Table'[Month] ) ) )(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous thanks for the help and thanks for the very clear response.
What is the benefit of creating measures when you sum the decimal value of the duration in seconds which you created as a calculated field instead of duplicating the worksWorked and transforming it into TotalSeconds
Also is there any way to show the values in hours and mins e.g. 11.00 is 11hr 00mins, 11.50 is 11hrs 30mins? My reason is its easier for readers but also when there is more data and hoursWorked then goes over 24hrs i think it makes more sense to talk about days worked instead of hours days
Hi mobul ,
You can create measures.
Format 1 = var _a=[total hoursWorked per dept per month] var _b=ROUNDDOWN(_a,0) var _c=_a-_b return SWITCH(TRUE(), _b=0,_c*60 & "m", _c=0, _b& "h", _b&"h"&(_a-_b)*60&"m")Format 2 = var _a=[total hoursWorked per employeeID per month] var _b=ROUNDDOWN(_a,0) var _c=_a-_b return SWITCH(TRUE(), _b=0,_c*60 & "m", _c=0, _b& "h", _b&"h"&(_a-_b)*60&"m")Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
