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.
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
Hi @Credible ,
Create a Calculated column
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:
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 @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.
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
