Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vito
Helper I
Helper I

Cumulative time difference summarized by person and date

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

TMS.jpg

I need a new table that is like this

Vito_0-1664444221220.png

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.

1 ACCEPTED SOLUTION

@Vito 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

16 REPLIES 16
tamerj1
Super User
Super User

Hi @Vito 
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
Super User
Super User

@Vito 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/3395....
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

TMS.png

 

@Vito 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

EMPREFDateTime
053001-Apr-228:25:00 AM
053001-Apr-224:00:00 PM
053001-Apr-221:34:00 PM
053001-Apr-2212:55:00 PM
027904-Apr-229:24:00 AM
027904-Apr-221:30:00 PM
027904-Apr-222:24:00 PM
027904-Apr-226:32:00 PM
027905-Apr-229:30:00 AM
027905-Apr-225:25:00 PM
027905-Apr-221:30:00 PM
027905-Apr-222:00:00 PM
053005-Apr-228:26:00 AM
053005-Apr-221:28:00 PM
053005-Apr-222:02:00 PM
053005-Apr-224:04:00 PM
027906-Apr-225:39:52 PM
053006-Apr-228:31:22 AM
027906-Apr-229:04:51 AM
027906-Apr-221:07:31 PM
053006-Apr-221:11:53 PM
053006-Apr-221:44:28 PM
027906-Apr-222:05:24 PM
053006-Apr-224:16:34 PM
027907-Apr-228:25:11 AM
027907-Apr-221:29:20 PM
053007-Apr-221:32:00 PM
053007-Apr-228:16:00 AM
027907-Apr-222:04:32 PM
027907-Apr-224:36:35 PM
053007-Apr-224:40:00 PM
053007-Apr-222:02:00 PM

@Vito 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

@Vito 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

@Vito 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]))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

@Vito 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]))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

 

Regards,

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.

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. 😔

@Vito 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors