cancel
Showing results 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

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

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.

1 ACCEPTED SOLUTION
Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
16 REPLIES 16
Super User

Hi @Vito

``````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``````

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I
 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
Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

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

Regards,

Helper I

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.

Helper I

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

Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.