topic Re: Time Measure - Productive hours with duplicates and overlaps. in DAX Commands and Tips

Time Measure - Productive hours with duplicates and overlaps.

Credible — Mon, 22 Jun 2020 02:06:14 GMT

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

Re: Time Measure - Productive hours with duplicates and overlaps.

stevedep — Mon, 22 Jun 2020 05:53:44 GMT

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.

Re: Time Measure - Productive hours with duplicates and overlaps.

Anonymous — Mon, 22 Jun 2020 06:03:36 GMT

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)

Re: Time Measure - Productive hours with duplicates and overlaps.

stevedep — Wed, 24 Jun 2020 05:11:38 GMT

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.

Re: Time Measure - Productive hours with duplicates and overlaps.

Credible — Wed, 24 Jun 2020 04:27:00 GMT

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

Re: Time Measure - Productive hours with duplicates and overlaps.

stevedep — Wed, 24 Jun 2020 19:56:10 GMT

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.

Re: Time Measure - Productive hours with duplicates and overlaps.

Anonymous — Mon, 05 Apr 2021 15:40:43 GMT

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

Re: Time Measure - Productive hours with duplicates and overlaps.

stevedep — Tue, 06 Apr 2021 15:59:38 GMT

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.

Re: Time Measure - Productive hours with duplicates and overlaps.

Anonymous — Wed, 07 Apr 2021 09:16:58 GMT

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

Cheers

Re: Time Measure - Productive hours with duplicates and overlaps.

Anonymous — Wed, 07 Apr 2021 11:59:29 GMT

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

Re: Time Measure - Productive hours with duplicates and overlaps.

stevedep — Wed, 07 Apr 2021 15:55:19 GMT

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-p/963216

Re: Time Measure - Productive hours with duplicates and overlaps.

Anonymous — Wed, 07 Apr 2021 16:12:57 GMT

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

Test file

Re: Time Measure - Productive hours with duplicates and overlaps.

stevedep — Sat, 10 Apr 2021 05:46:47 GMT

Hi,

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

Kind regards Steve