Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Credible
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 IDClient IDStartEnd
12525/10/2019 9:3025/10/2019 10:30
11525/10/2019 11:0025/10/2019 11:30
18625/10/2019 11:0025/10/2019 12:00
12125/10/2019 11:0025/10/2019 12:00
1725/10/2019 11:0025/10/2019 12:00
116925/10/2019 11:0025/10/2019 12:00
18528/10/2019 9:0028/10/2019 10:00
19128/10/2019 9:0028/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

Hi @Credible ,

 

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

timeoverlap.jpg

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. 

View solution in original post

12 REPLIES 12
Seb_ABZ
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.

 

Seb_ABZ_0-1617636476953.png

I would expect the following results for the asset A_01

Seb_ABZ_1-1617636817453.png

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

Seb_ABZ_2-1617636988608.png

 

 

Thanks in advance for your help

@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

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

Test file 

 

 

Hi,

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

Kind regards Steve

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

 

Cheers

harshnathani
Community Champion
Community Champion

Hi @Credible ,

 

Create a Calculated column

 

CALCULATE(Min('Table'[Start]),FILTER( ALLEXCEPT('Table','Table'[Column]), 'Table'[Client ID] = MAX('Table'[Client ID])))
 
1.jpg
 
 
 
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)
 
2.JPG
 

Regards,
Harsh Nathani

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

Hi @Credible ,

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

dimteduration.jpg

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.

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 IDStartEnd
127/07/2019 9:0027/07/2019 10:00
127/07/2019 9:0027/07/2019 10:00
127/07/2019 9:0027/07/2019 10:00
127/07/2019 9:0027/07/2019 10:00
127/07/2019 9:0027/07/2019 10:00
127/07/2019 9:0027/07/2019 10:00
127/07/2019 10:0027/07/2019 11:00
129/07/2019 10:3029/07/2019 12:00
129/07/2019 10:3029/07/2019 12:00
129/07/2019 12:3029/07/2019 13:30
129/07/2019 12:3029/07/2019 13:30
129/07/2019 12:3029/07/2019 14:00
129/07/2019 12:3029/07/2019 14:00
129/07/2019 14:1529/07/2019 15:15
129/07/2019 22:0029/07/2019 22:30
130/07/2019 9:0030/07/2019 10:00
130/07/2019 9:0030/07/2019 10:30
130/07/2019 9:0030/07/2019 10:30
130/07/2019 9:0030/07/2019 10:30
130/07/2019 9:0030/07/2019 10:30
130/07/2019 9:3030/07/2019 9:30
130/07/2019 10:0030/07/2019 11:30
130/07/2019 10:3030/07/2019 12:00
130/07/2019 10:3030/07/2019 12:00
130/07/2019 11:0030/07/2019 12:00
130/07/2019 12:3030/07/2019 12:30
130/07/2019 12:3030/07/2019 12:30
130/07/2019 12:3030/07/2019 13:00
130/07/2019 12:3030/07/2019 14:00
130/07/2019 12:3030/07/2019 14:00
130/07/2019 12:3030/07/2019 14:00
130/07/2019 12:3030/07/2019 14:00
130/07/2019 13:0030/07/2019 14:00
130/07/2019 14:1530/07/2019 15:15
130/07/2019 22:3030/07/2019 23:30

Hi @Credible ,

 

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

timeoverlap.jpg

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
Memorable Member
Memorable Member

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:

netduration.jpg

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors