Forum Discussion

mobul's avatar
mobul
Frequent Visitor
2 years ago
Solved

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...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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. 

mobul's avatar
mobul
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.