Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculations with time

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. :)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

      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