Aggregating and Visualising Duration Data

[mobul]

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

1. 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.
2. If i duplicate the hours and change it to type decimal then I can sum but can't figure out how to
   1. ensure the result is accurate
   2. format the result so its user friendly in days, hours mins. 
3. 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

[Anonymous · Accepted solution]

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)/3600

Month = 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. 

[Anonymous · Accepted solution]

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)/3600

Month = 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. 

[mobul]

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

[Anonymous · Accepted solution]

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.