Calculations with time

[Anonymous]

Hello guys,

Is there any way that I can sum amount of time with dax in hh:mm:ss format? I want to keep track of the amount of time my employees are in lunch, break, or something like that. 

For examples

Employee //  Task // Start time// End time // Duration

Louis // Lunch // 11:03:15 am // 12: 13: 26 pm // 01:10:11

And being able to sum how much time he spents on lunch per week? Assuming the lunch time per day is just 1h.

Id like a measure that can show me the results in hh:mm:ss format and i havent found ANYTHING online either youtube 😞

Thanks

[Anonymous · Accepted solution]

Hi,

Could you please check the below link to the pbix i have created:

https://1drv.ms/u/s!AikPceQOhqFEhBXLUf0tmuSOsk4l?e=AaLqgy

I have added a calculated column to calculate the difference between starttime and endtime:

Lunch Per Day = PATHITEM(SUBSTITUTE(FORMAT( 'Table'[EndTime] - 'Table'[StartTime], "DD-MM-YYYY HH:MM:SS" )," ", "|"), 2 )

Also, added another column to display 'Start of Week' so that we can check the lunch hours on a weekly basis.

Then, i have created a measure to sum the time difference:

Weekly Lunch break sum = 
VAR v_WeeklySum = CALCULATE( SUM( 'Table'[Lunch Per Day] ), ALLEXCEPT( 'Table', 'Table'[Start of Week], 'Table'[Ename] ), 
FILTER( 'Table', 'Table'[Task] = "Lunch" )
)
RETURN
PATHITEM(
    SUBSTITUTE(FORMAT( v_WeeklySum, "DD-MM-YYYY HH:MM:SS" )," ", "|"), 2 
    )

This gives me below result:

Thanks.

Please accept this as a solution if it satisfies the requirement. Appreciate your Kudos. 🙂

[Anonymous]

@Anonymous  Hey, thanks so much for your response, actually it helped me a lot, im still kind of consufed about the process of getting the result haha i just still have a doubt, using the same result, on the 1st week the total lunch time is 2:20:27, what if the max lunch time per week is just 2:00:00, how can I calculate the difference between both? So i can know which employee is spending more time in lunch than the others

Thanks