Forum Discussion
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.
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.
16 Replies
- Greg_Deckler
Community Champion
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- 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.
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.
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.
- tamerj1
Hi Anonymous
Please tryHours 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
