cancel
Showing results for
Did you mean:
Regular Visitor

## Time Measure - Productive hours with duplicates and overlaps.

Hi,

I am looking for some help calculating productive hours where employees can record multiple entries when working with groups of clients.

For example

 Worker ID Client ID Start End 1 25 25/10/2019 9:30 25/10/2019 10:30 1 15 25/10/2019 11:00 25/10/2019 11:30 1 86 25/10/2019 11:00 25/10/2019 12:00 1 21 25/10/2019 11:00 25/10/2019 12:00 1 7 25/10/2019 11:00 25/10/2019 12:00 1 169 25/10/2019 11:00 25/10/2019 12:00 1 85 28/10/2019 9:00 28/10/2019 10:00 1 91 28/10/2019 9:00 28/10/2019 10:00

So for the 25/10/2019 for Worker 1 it should return 2 hours ie 2 hours of the workers time was spent providing services.

Thanks

1 ACCEPTED SOLUTION
Super User I

Hi @Credible ,

I changed the approach by adjusting the start time. This is giving the expected results:

The calc columns now are:

TimeSpentV2 =
IF([Rank]=1 && 'Table'[AbsorbedV2]=FALSE();

Using Rank (unchanged):

Rank =
var __WorkerID = [Worker ID]
var __Start = [Start]
var __End = [End]
return
RANKX (
FILTER ( ALL('Table'); [Worker ID] = __WorkerID && [Start] = __Start && [End] = __End ) ;
[RankSupport]
)

Using (unchanged):

RankSupport = RANDBETWEEN(1;100)

Check absorbed (changed):

AbsorbedV2 =
var __Startdate     =   [Start].[Date]
var __StartDateTime =   [Start]
var __Enddate       =   [end]
var __WorkerID     =    [Worker ID]
var __Client        =   [Client ID]

var __check_absorbed = FILTER(ALL('Table');    [Worker ID] = __WorkerID && [Start].[Date] = __Startdate &&
[Start] <= __StartDateTime && [End] >= __Enddate
&& [Client ID] <> __Client && [Rank]=1)

return
//CONCATENATEX(__check_absorbed;[Client ID])
IF(
COUNTROWS(__check_absorbed)
=0;FALSE();TRUE())

And the new start time (new):

var __Startdate         =   [Start].[Date]
var __StartDateTime     =   'Table'[Start]
var __Enddate           =   [end]
var __WorkerID          =   [Worker ID]
var __Client            =   [Client ID]

var __Filter        =    FILTER(ALL('Table');'Table'[Worker ID]=__WorkerID && [Start].[Date] = __Startdate &&
[End] > __StartDateTime && [End] < __Enddate && [Rank] = 1 && [AbsorbedV2]=FALSE()
)
return
CALCULATE(MAX('Table'[End]);__Filter)

That should be it. I sent your file via a PM.

Kind regards, Steve.

Proud to be a Super User!

12 REPLIES 12
Frequent Visitor

Hello All,

I have a pretty similar problem, except in my case the jobs can spread over several days and I need to figure out how to deal with opening hours.

Let me explain, I have jobs recorded against assets. I would like to know how long the workshop is used over a period.

Workshop is opened from 08:00 to 20:00 and is closed the Sunday.

I would expect the following results for the asset A_01

And below the calculation details for the asset A_01 for the period of March

Super User I

Hi @Seb_ABZ ,

Kind regards, Steve.

Proud to be a Super User!

Frequent Visitor

@stevedep I reviewed the post and I tested on my case but unfortunately that doesn't work since this option doesn't deal with overlaps (in my case the workorders W_03, W_04, W_05).

Any idea?

Thanks again

Super User I

If you can share the sample data I might find a moment to take a look.

Proud to be a Super User!

Frequent Visitor

Thanks a lot Steve, please find in attachment the link to my test file

Super User I

Hi,

Kind regards Steve

Proud to be a Super User!

Frequent Visitor

Thanks a lot Steve, I'm going to have a look on this post.

Cheers

Super User III

Hi @Credible ,

Create a Calculated column

CALCULATE(Min('Table'[Start]),FILTER( ALLEXCEPT('Table','Table'[Column]), 'Table'[Client ID] = MAX('Table'[Client ID])))

Create the  measure

Service Rendered (in Hours) =

var a = CALCULATE(Min('Table'[Start]),FILTER(ALL('Table'),'Table'[Date Column] = MAX('Table'[Date Column]) && 'Table'[Worker ID] = MAX('Table'[Worker ID])))
var b = CALCULATE(MAX('Table'[End]),FILTER(ALL('Table'),'Table'[Date Column] = MAX('Table'[Date Column]) && 'Table'[Worker ID] = MAX('Table'[Worker ID])))
RETURN

DIVIDE(DATEDIFF(a,b,MINUTE),60)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Super User I

Hi @Credible ,

I broke it up in a bunch of calc columns, here they are:

Dependant =
var __Startdate     =   [Start].[Date]
var __StartDateTime =   [Start]
var __Enddate       =   [end]
var __WorkerID     =    [Worker ID]
var __Client        =   [Client ID]

var __check_dependant = FILTER(ALL('Table');    'Table'[Worker ID] = __WorkerID && [Start].[Date] = __Startdate &&
(
([end] > __StartDateTime && [Start] >= __StartDateTime) // end date of clientactivity beyond start of current & started before current
&& //or
([Start] < __Enddate && [end] > __Enddate)
) // started before and end later
)
return
IF(COUNTROWS(__check_dependant)=0;FALSE();TRUE())

var __Startdate         =   [Start].[Date]
var __StartDateTime     =   'Table'[Start]
var __Enddate           =   [end]
var __WorkerID          =   [Worker ID]
var __Client            =   [Client ID]

var __Filter        =    FILTER(ALL('Table');'Table'[Worker ID]=__WorkerID && [Start].[Date] = __Startdate &&
[Start] >= __StartDateTime && [end] > __Enddate && [Start] < __Enddate
)
return
CALCULATE(MAX('Table'[End]);__Filter)
Absorbed =
var __Startdate     =   [Start].[Date]
var __StartDateTime =   [Start]
var __Enddate       =   [end]
var __WorkerID     =    [Worker ID]
var __Client        =   [Client ID]

var __check_absorbed = FILTER(ALL('Table');    [Worker ID] = __WorkerID && [Start].[Date] = __Startdate &&
__StartDateTime >= [Start] && __StartDateTime < [End] && __Enddate > [End])

return
IF(COUNTROWS(__check_absorbed)=0;FALSE();TRUE())
RankSupport = RANDBETWEEN(1;100)
Rank =
var __WorkerID = [Worker ID]
var __Start = [Start]
var __End = [End]
return
RANKX (
FILTER ( ALL('Table'); [Worker ID] = __WorkerID && [Start] = __Start && [End] = __End ) ;
[RankSupport]
)
NewEnd = IF([Rank]=1 && [Absorbed]=FALSE;
Timespent = IF(NOT(ISBLANK('Table'[NewEnd]));DATEDIFF([Start];[NewEnd];MINUTE)/60;BLANK())

Looking forward to hearing how this works.

Kind regards, Steve.

Proud to be a Super User!

Regular Visitor

Hi @stevedep ,

The calculated columns are working well from a performance point of view. There is still some issues with calculation though.

Consider the data set below. I would expect timespent to output:

• 27/07/2019 2 hours
• 29/07/2019 4.5 hours
• 30/07/2019 6.5 hours

The current results output 1, 4.5 and 3.5 respectively.

 Worker ID Start End 1 27/07/2019 9:00 27/07/2019 10:00 1 27/07/2019 9:00 27/07/2019 10:00 1 27/07/2019 9:00 27/07/2019 10:00 1 27/07/2019 9:00 27/07/2019 10:00 1 27/07/2019 9:00 27/07/2019 10:00 1 27/07/2019 9:00 27/07/2019 10:00 1 27/07/2019 10:00 27/07/2019 11:00 1 29/07/2019 10:30 29/07/2019 12:00 1 29/07/2019 10:30 29/07/2019 12:00 1 29/07/2019 12:30 29/07/2019 13:30 1 29/07/2019 12:30 29/07/2019 13:30 1 29/07/2019 12:30 29/07/2019 14:00 1 29/07/2019 12:30 29/07/2019 14:00 1 29/07/2019 14:15 29/07/2019 15:15 1 29/07/2019 22:00 29/07/2019 22:30 1 30/07/2019 9:00 30/07/2019 10:00 1 30/07/2019 9:00 30/07/2019 10:30 1 30/07/2019 9:00 30/07/2019 10:30 1 30/07/2019 9:00 30/07/2019 10:30 1 30/07/2019 9:00 30/07/2019 10:30 1 30/07/2019 9:30 30/07/2019 9:30 1 30/07/2019 10:00 30/07/2019 11:30 1 30/07/2019 10:30 30/07/2019 12:00 1 30/07/2019 10:30 30/07/2019 12:00 1 30/07/2019 11:00 30/07/2019 12:00 1 30/07/2019 12:30 30/07/2019 12:30 1 30/07/2019 12:30 30/07/2019 12:30 1 30/07/2019 12:30 30/07/2019 13:00 1 30/07/2019 12:30 30/07/2019 14:00 1 30/07/2019 12:30 30/07/2019 14:00 1 30/07/2019 12:30 30/07/2019 14:00 1 30/07/2019 12:30 30/07/2019 14:00 1 30/07/2019 13:00 30/07/2019 14:00 1 30/07/2019 14:15 30/07/2019 15:15 1 30/07/2019 22:30 30/07/2019 23:30
Super User I

Hi @Credible ,

I changed the approach by adjusting the start time. This is giving the expected results:

The calc columns now are:

TimeSpentV2 =
IF([Rank]=1 && 'Table'[AbsorbedV2]=FALSE();

Using Rank (unchanged):

Rank =
var __WorkerID = [Worker ID]
var __Start = [Start]
var __End = [End]
return
RANKX (
FILTER ( ALL('Table'); [Worker ID] = __WorkerID && [Start] = __Start && [End] = __End ) ;
[RankSupport]
)

Using (unchanged):

RankSupport = RANDBETWEEN(1;100)

Check absorbed (changed):

AbsorbedV2 =
var __Startdate     =   [Start].[Date]
var __StartDateTime =   [Start]
var __Enddate       =   [end]
var __WorkerID     =    [Worker ID]
var __Client        =   [Client ID]

var __check_absorbed = FILTER(ALL('Table');    [Worker ID] = __WorkerID && [Start].[Date] = __Startdate &&
[Start] <= __StartDateTime && [End] >= __Enddate
&& [Client ID] <> __Client && [Rank]=1)

return
//CONCATENATEX(__check_absorbed;[Client ID])
IF(
COUNTROWS(__check_absorbed)
=0;FALSE();TRUE())

And the new start time (new):

var __Startdate         =   [Start].[Date]
var __StartDateTime     =   'Table'[Start]
var __Enddate           =   [end]
var __WorkerID          =   [Worker ID]
var __Client            =   [Client ID]

var __Filter        =    FILTER(ALL('Table');'Table'[Worker ID]=__WorkerID && [Start].[Date] = __Startdate &&
[End] > __StartDateTime && [End] < __Enddate && [Rank] = 1 && [AbsorbedV2]=FALSE()
)
return
CALCULATE(MAX('Table'[End]);__Filter)

That should be it. I sent your file via a PM.

Kind regards, Steve.

Proud to be a Super User!

Super User I

Hi @Credible ,

I had this one lying on the shelf, this is an interesting DAX challenge since we do not want to duplicate time and consider that some activities for another client start while the timer might be running for another. Here is the code:

Measure =
// first get distinct times per worker, per date
var _NewTbl = CALCULATETABLE(SUMMARIZE('Table';'Table'[Worker ID];'Table'[Start];'Table'[End]);ALLEXCEPT('Table';'Table'[Worker ID];'Table'[Start];'Table'[End]))
return
SUMX(_NewTbl; // iterating the rows to calculate the values for each row, of these values the sum is taken
CALCULATE( // calculate to force context transition, set the context to the row being iterated!
// set the outer variable values, for the row being interated.
var __startdate =       SELECTEDVALUE('Table'[Start])
var __enddate =         SELECTEDVALUE('Table'[end])
var __EQ    =           SELECTEDVALUE('Table'[Worker ID])
var __Contract =        SELECTEDVALUE('Table'[Client ID])

// calculate a new duration based on the adjusted end date, taken from the client which extends the clienttime being interated.
var __adjustedduration =    CALCULATE( // for the row being interated we iterate the table again (for the worker) to find clientactivities which might extend its duration
SUMX('Table';  DATEDIFF(__startdate;[End];MINUTE)) ;
// below is the filter context to get all clientactivities for this worker that might extend.
FILTER(ALL('Table'); 'Table'[Start] > __startdate && 'Table'[end] > __enddate && 'Table'[Start] < __enddate && 'Table'[Worker ID] = SELECTEDVALUE('Table'[Worker ID]))
)
// below code checks to see if there are any clientactivity which might extend its duration.
var __check_dependant = FILTER(ALL('Table');
('Table'[end] > __startdate && 'Table'[Start] < __startdate) // end date of clientactivity beyond start of current & started before current
|| //or
('Table'[Start] < __enddate && 'Table'[end] > __enddate) // started before and end later
&&  'Table'[Worker ID] = SELECTEDVALUE('Table'[Worker ID])
)
return
IF(COUNTROWS(__check_dependant)=0; // if the clientactivity is independant / disjunct just takes its own start and end
DATEDIFF(MIN('Table'[Start]);MIN('Table'[End]);MINUTE) / 60;
// if not take the adjusted duration
)
)

With the output visible here:

The challenge with this one is to make sure it aggregates well, I therefore show the hierarchy to demonstrate its support.

The file is available here

Does this answer your question? If so, please mark as solution. Thumbs up for the effort is appreciated.

Kind regards,

Steve.

Proud to be a Super User!

Announcements