Time Measure - Productive hours with duplicates and overlaps.

[Credible]

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

[Anonymous]

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

Thanks in advance for your help

[stevedep]

Hi @Anonymous ,

This post might help you: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Availability-ratio-of-applications/m-p/1735933#M36038 

Kind regards, Steve. 

[Anonymous]

@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

[stevedep]

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

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[Anonymous]

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

Test file 

[stevedep]

Hi,

I can't download files. Please paste your data as the link describes.

Kind regards Steve

[Anonymous]

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

Cheers

[harshnathani]

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)

[stevedep]

Hi @Credible ,

I made a calculated column version to address the performance issue:

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())
                                                

Adjusted_EndTime = 
        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;
                IF([Dependant]=TRUE;[Adjusted_EndTime];[End]))

Timespent = IF(NOT(ISBLANK('Table'[NewEnd]));DATEDIFF([Start];[NewEnd];MINUTE)/60;BLANK())

Looking forward to hearing how this works.

Kind regards, Steve.

[Credible]

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

[stevedep · Accepted solution]

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(); 
    IF(NOT(ISBLANK('Table'[Adjusted_StartTime]));DATEDIFF([Adjusted_StartTime];[End];MINUTE)/60;DATEDIFF([Start];[End];MINUTE)/60);BLANK())

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):

Adjusted_StartTime = 
        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. 

[stevedep]

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
            __adjustedduration / 60)
        )
)

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.