Cumulative time difference summarized by person and date

[Anonymous]

Good morning,

I have a dataset of employees the clock in and out every day.

I need to summarise the data so that I can calculate how many hours each of them worked each day.

A sample of the data looks like this

I need a new table that is like this

An additional issue here is the the clocking in not showed in chronological order and need to be ordered before calculating the time differences.

Many thanks for your help.

[tamerj1]

Hi @Anonymous 
Please try

Hours worked =
MAX ( 'Table'[Time] ) - MIN ( 'Table'[Time] )

Or

Hours worked =
VAR TotalMinutes =
    SUMX ( 'Table', DATEDIFF ( 'Table'[ClockIn], 'Table'[ClockOut], MINUTE ) )
VAR Minutes =
    FORMAT ( MOD ( TotalMinutes, 60 ), "00" )
VAR Hours =
    FORMAT ( QUOTIENT ( TotalMinutes, 60 ), "00" )
RETURN
    Hours & ":" & Minutes

[Greg_Deckler]

@Anonymous Well, seems like you could just subtract the clock out time from the clock in time but impossible to tell with everything blacked out what the data looks like. 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[Anonymous]

Hi @Greg_Deckler 

this is what I get when I apply your code.

Is there a way to order the Time and then calculate the differences?

I have addedd also the other variables results (Previous, PreviousDate and Current) to show what I get from those Variables.

[Greg_Deckler]

@Anonymous Give me the first 8 rows as text, just the employ column, date column and time column. Just use the table tool to post the data. It will take me three times as long to type it out versus writing the DAX.

[Anonymous]

Hi @Greg_Deckler 

EMPREF	Date	Time
0530	01-Apr-22	8:25:00 AM
0530	01-Apr-22	4:00:00 PM
0530	01-Apr-22	1:34:00 PM
0530	01-Apr-22	12:55:00 PM
0279	04-Apr-22	9:24:00 AM
0279	04-Apr-22	1:30:00 PM
0279	04-Apr-22	2:24:00 PM
0279	04-Apr-22	6:32:00 PM
0279	05-Apr-22	9:30:00 AM
0279	05-Apr-22	5:25:00 PM
0279	05-Apr-22	1:30:00 PM
0279	05-Apr-22	2:00:00 PM
0530	05-Apr-22	8:26:00 AM
0530	05-Apr-22	1:28:00 PM
0530	05-Apr-22	2:02:00 PM
0530	05-Apr-22	4:04:00 PM
0279	06-Apr-22	5:39:52 PM
0530	06-Apr-22	8:31:22 AM
0279	06-Apr-22	9:04:51 AM
0279	06-Apr-22	1:07:31 PM
0530	06-Apr-22	1:11:53 PM
0530	06-Apr-22	1:44:28 PM
0279	06-Apr-22	2:05:24 PM
0530	06-Apr-22	4:16:34 PM
0279	07-Apr-22	8:25:11 AM
0279	07-Apr-22	1:29:20 PM
0530	07-Apr-22	1:32:00 PM
0530	07-Apr-22	8:16:00 AM
0279	07-Apr-22	2:04:32 PM
0279	07-Apr-22	4:36:35 PM
0530	07-Apr-22	4:40:00 PM
0530	07-Apr-22	2:02:00 PM

[Greg_Deckler]

@Anonymous Perfect. Thanks. See attached PBIX below signature. The formulas I used were:

Previous = MAXX(FILTER('Table',[EMPREF] = EARLIER('Table'[EMPREF]) && [Date] = EARLIER('Table'[Date]) && [Time] < EARLIER('Table'[Time])),[Time])

Diff = IF(ISBLANK([Previous]),BLANK(),[Time] - [Previous])

Table 2 = SUMMARIZE('Table',[EMPREF],[Date],"Hours",SUM('Table'[Diff]))

You could do it all in one statement using ADDCOLUMNS if you wanted. You may have to do more than I did to monkey around with calculating the hours correctly instead of relying on DAX's kind of hokey way of doing things but this should get you pretty close.

[Anonymous]

Hi @Greg_Deckler ,

Thank you very much for your help.

We are almost there, the calculation take into account the break time (i.e. between the third and second cloacking) ending up overcalculating the hours.

Is there, in your opinion, a trick that can do the job?

Again, I really appreciate your help.

[Greg_Deckler · Accepted solution]

@Anonymous Well, can we make the assumption that there are always 4 clock-in and outs per day? Or, is there a flag that says whether it was a clock in or a clock out? Another option would be to only calculate the difference if it is the max time and the minimum time that is greater than the absolute minimum time for the day (tricky but doable). Let me know if any of those seem feasible.

[Anonymous]

Thank you @Greg_Deckler .

At this stage I am putting the exploration on pause as I see that the data is not very friendly to us. I appreciate the help, at least I have learned something new.

[Greg_Deckler]

@Anonymous Sure, understood, or you can fix it by using this formula for Previous column:

Previous = 
    VAR __Date = [Date]
    VAR __Time = [Time]
    VAR __Emp = [EMPREF]
    VAR __MaxTime = MAXX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date),[Time])
    VAR __MinTime = MINX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date),[Time])
    VAR __MinPlus1 = MINX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date && [Time] > __MinTime),[Time])
    VAR __Time1 = 
        SWITCH(TRUE(),
            [Time] = __MaxTime, __MaxTime,
            [Time] = __MinPlus1, __MinPlus1,
            BLANK()
        )
RETURN
    IF(__Time1 = BLANK(),BLANK(), MAXX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date && [Time] < __Time1),[Time]))

[Anonymous]

Hi @Greg_Deckler ,

thank you for that one I am looking at it now.

Whould you mind to quickly explain the mechanics of this formula?

I would be very much appreciated.

[Greg_Deckler]

@Anonymous Sure, it basically does the third "tricky" option that I described:

Previous = 
// First, get some values for the context of our current row
    VAR __Date = [Date]
    VAR __Time = [Time]
    VAR __Emp = [EMPREF]
// Get the maximum time clock in time for the employee on that date, we want to figure out the difference for this row from the previous clock in time
    VAR __MaxTime = MAXX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date),[Time])
// Get the minimum clock in time for the employee on that date, we don't want to figure out the difference for this row, we want the next clock in
    VAR __MinTime = MINX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date),[Time])
// Using our __MinTime, we find the next clock in time for that employee on that date, this is the row we want to caclulate the difference for from the original clock in time on that date
    VAR __MinPlus1 = MINX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date && [Time] > __MinTime),[Time])
// We now decide if we are in a row where we want to calculate the difference from the previous  clock in time, either the __MaxTime or the __MinPlus1 time. If the time in our current row is equal to either of those, then we want to calculate the difference from the previous clock-in
    VAR __Time1 = 
        SWITCH(TRUE(),
            [Time] = __MaxTime, __MaxTime,
            [Time] = __MinPlus1, __MinPlus1,
            BLANK()
        )
RETURN
// So, if we aren't on a row we want to calculate the difference from the previous time, then we return BLANK(), otherwise, we figur out the previous clock in time which is the maximum time that is for the same employee, date and is less than our current row's time (__Time1). We grab the [Time] for that row. We now only have a Previous value for rows where we want to calculate the difference from the current row's time and the previous clock in time. 
    IF(__Time1 = BLANK(),BLANK(), MAXX(FILTER('Table',[EMPREF] = __Emp && [Date] = __Date && [Time] < __Time1),[Time]))

[Anonymous]

Hi Greg,
aplogies for coming back to you so late. 

Thakn you very much for your explanation, it is really helpful.

Regards,

[Anonymous]

Hi Greg,

unfortunately the clocking direction can't be used in this instance as this field is not constantly used, therefore, is unreliable. I had to redact the picture as there are informations not useful or can't be published.

The only field I can use are those three and the only way to correctly subtract the In/Out time is to order it in ascending way. But it need to be done only after filtering for each employee and each day.

[Anonymous]

Thank you for your quick reply.

I think those measure are not very helpful in this instance as I need brand new summarized table that will be used to perform other calculations later on. 😔

[Greg_Deckler]

@Anonymous It's the same technique, you would just create a table in DAX with the appropriate calculation logic. If you can post sample data as text I can be a lot more specific in terms of a solution.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.