Title: How do I assess and create a conditional output where a rows' DateTime value sits between an earlier row's Start/End DateTime?
Tags: datetime;dax;powerquery
Question: I'm a little stumped.
I need to come up with a query to exclude an entry where :
For the same 'ClientN' value, if the Start Time is after a previous start time AND before a previous End Time, then it's excluded (mentioned below as exclude)
But, if a second time extends beyond the first entry, then the extension of the time is counted (mentioned in the table as 'partial include').
Below is some data that has had this rule manually applied.
I was planning on using PowerQuery for this, but I am open to other ideas.
The premise is to remove instances of 'double counts' against the same project/client. (DT is in d/mm/yyyy HH:mm)
Start Time
End Time
Duration
ClientN
ProjectN
Decision
6/01/2022 12:30
6/01/2022 13:12
0.00:42:00
Client1
Project6
include
6/01/2022 13:14
6/01/2022 13:44
0.00:30:18
Client1
Project6
include
6/01/2022 13:44
6/01/2022 14:18
0.00:33:36
Client2
Project3
include
6/01/2022 14:18
6/01/2022 15:05
0.00:47:24
Client1
Project6
include
6/01/2022 15:12
6/01/2022 15:39
0.00:26:06
Client2
Project3
include
6/01/2022 21:00
6/01/2022 22:00
0.01:00:00
Client2
Project5
include
7/01/2022 8:30
7/01/2022 9:00
0.00:30:00
Client1
Project1
include
7/01/2022 12:00
7/01/2022 13:00
0.01:00:00
Client1
Project7
include
7/01/2022 13:30
7/01/2022 14:30
0.01:00:00
Client1
Project6
include
7/01/2022 17:10
7/01/2022 17:42
0.00:32:12
Client2
Project8
include
10/01/2022 13:45
10/01/2022 14:45
0.01:00:00
Client1
Project6
include
10/01/2022 13:46
10/01/2022 13:59
0.00:12:53
Client1
Project1
exclude
10/01/2022 13:59
10/01/2022 14:00
0.00:01:20
Client2
Project2
include
10/01/2022 14:00
10/01/2022 14:09
0.00:08:18
Client1
Project1
exclude
10/01/2022 14:09
10/01/2022 14:10
0.00:01:20
Client2
Project2
include
10/01/2022 14:09
10/01/2022 14:11
0.00:02:02
Client1
Project1
exclude
10/01/2022 14:11
10/01/2022 14:12
0.00:00:38
Client2
Project2
include
10/01/2022 14:12
10/01/2022 14:31
0.00:19:36
Client1
Project1
exclude
10/01/2022 14:31
10/01/2022 14:32
0.00:00:59
Client2
Project2
include
10/01/2022 14:32
10/01/2022 14:32
0.00:00:02
Client1
Project1
exclude
10/01/2022 14:32
10/01/2022 14:33
0.00:01:12
Client2
Project2
include
10/01/2022 14:33
10/01/2022 14:34
0.00:00:59
Client1
Project1
exclude
10/01/2022 14:34
10/01/2022 14:39
0.00:04:20
Client2
Project2
include
10/01/2022 14:39
10/01/2022 14:42
0.00:03:14
Client1
Project1
exclude
10/01/2022 14:43
10/01/2022 14:55
0.00:12:14
Client1
Project1
partial include
I was playing around with formulas:
Column =
VAR __Current = [end time]
VAR __PreviousDate = MAXX(FILTER('detailedreportextracts__small',[start Time] < EARLIER([start Time] ) &amp;&amp; [ClientN] = earlier([ClientN]) ),[start Time])
var __Previous = MAXX(filter(detailedreportextracts__small,[start Time]= __PreviousDate),[end Time])
return
__Current - __Previous
The issue appears to be that it only looks back one data row.
So, where I see a negative value on the first exclusion (this is correct), I would also think I would see negative values below this line on the lines that I have marked as 'exclude'.
I might have the columns in the wrong spot in the formula. I did swap them around, but then the row calculated value didn't work for me.
After fluffing around for another few hours with tireless googling, I've been trying to play with: https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Pulling apart the DAX queries as they stand, so I can understand them better.
I'm not sure if it's the best way to do it, but I'm trying to extract the 2:45pm (circled) into another column, to then check if the [end time] is < the earlier larger end time.
But, for whatever reason, the 'earlier' function doesn't seem to look back very many rows earlier.
In my latest attempt: How to Compare the Current Row to the Previous Row by Category Using DAX
Again, my results seem to concur that I'm not able to look back further rows to maintain that 'max' value found earlier.
Any help would be greatly appreciated.
Comment: Your explanation and examples are not clear to me.  You write *"remove instances of 'double counts' against the same project/client"* but your example seems as if you are only removing instances against the same client.


Here is another answer: See if this works for you. Assumes Table1 includes everything EXCEPT the decision column.  It will basically generate a table of non-overlapping time spent by project/client
Based on aggregate continuous &quot;transitive&quot; overlapping time intervals
function process
(xtable)=&gt;
// for each group, compare each list against all lists in column Custom, and merge those that overlap
let Source= Table.Buffer(xtable),
#&quot;Added Custom&quot;= Table.AddColumn(
Source,
&quot;Custom2&quot;,
each let
begin = [Custom],
myclient=[ClientN],
myproject=[ProjectN]
List.Accumulate (
Table.SelectRows(Source,each [ClientN]=myclient and [ProjectN]=myproject)[Custom],
begin,
(state,current)=&gt; if List.ContainsAny(state,current) then List.Distinct(List.Combine({current,state})) else state
// count the number of changes made from original version. If this is not zero, we will recurse the changes
x= List.Sum(List.Transform(List.Positions(#&quot;Added Custom&quot;[Custom]), each if #&quot;Added Custom&quot;[Custom]{_} = #&quot;Added Custom&quot;[Custom2]{_} then 0 else 1)),
RemovePrioCustom= Table.RemoveColumns(#&quot;Added Custom&quot;,{&quot;Custom&quot;}),
AddNewCustom= Table.RenameColumns(RemovePrioCustom,{{&quot;Custom2&quot;, &quot;Custom&quot;}}),
recursive = if x=0 then AddNewCustom else @process( AddNewCustom)
in recursive
let Source = Excel.CurrentWorkbook(){[Name=&quot;Table1&quot;]}[Content],
#&quot;Changed Type&quot; = Table.TransformColumnTypes(Source,{{&quot;Start Time&quot;, type datetime}, {&quot;End Time&quot;, type datetime}, {&quot;Duration&quot;, type duration}, {&quot;ClientN&quot;, type text}, {&quot;ProjectN&quot;, type text}}),
#&quot;Added Index&quot; = Table.AddIndexColumn(#&quot;Changed Type&quot;, &quot;Index&quot;, 0, 1, Int64.Type),
#&quot;Added Custom&quot; = Table.AddColumn(#&quot;Added Index&quot;, &quot;Custom&quot;,
(i)=&gt;Table.SelectRows(#&quot;Added Index&quot;, each [ClientN]=i[ClientN] and [ProjectN]=i[ProjectN] and
([Start Time]&gt;=i[Start Time] and [End Time]<=i[End Time] or
[Start Time]<=i[Start Time] and [End Time]&gt;=i[End Time] or
[Start Time]<=i[Start Time] and [End Time]<=i[End Time] and [End Time]&gt;=i[Start Time]or
[Start Time]&gt;=i[Start Time] and [End Time]&gt;=i[End Time] and [Start Time] <=i[End Time])
)[Index]
MergeOverlap=  process(#&quot;Added Custom&quot;),
#&quot;Added Custom1&quot; = Table.AddColumn(MergeOverlap, &quot;StartMin&quot;, each List.Min(List.Transform([Custom], each MergeOverlap[Start Time]{_})),type datetime),
#&quot;Added Custom2&quot; = Table.AddColumn(#&quot;Added Custom1&quot;, &quot;EndMax&quot;, each List.Max(List.Transform([Custom], each #&quot;Added Custom1&quot;[End Time]{_})), type datetime),
#&quot;Removed Columns&quot; = Table.RemoveColumns(#&quot;Added Custom2&quot;,{&quot;Start Time&quot;, &quot;End Time&quot; ,&quot;Duration&quot;,&quot;Index&quot;, &quot;Custom&quot;}),
#&quot;Removed Duplicates&quot; = Table.Distinct(#&quot;Removed Columns&quot;, {&quot;ClientN&quot;, &quot;ProjectN&quot;,&quot;StartMin&quot;, &quot;EndMax&quot;}),
#&quot;Sorted Rows&quot; = Table.Sort(#&quot;Removed Duplicates&quot;,{{&quot;ClientN&quot;, Order.Ascending}, {&quot;ProjectN&quot;, Order.Ascending}, {&quot;StartMin&quot;, Order.Ascending}})
in #&quot;Sorted Rows&quot;
Comment for this answer: I modified my link to produce code above that resembles what I think you want. Let me know how it goes
Comment for this answer: Oh wow, this, and the other link you included, in theory, looks exactly on the money! I'll test it asap, but thank you so much.
Aside from column pivoting and having a whole number of columns to determine if the start/end dates were changing etc for each client etc and making a big mess in excel, I didn't really have any idea how this could be implemented.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:03/15/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<datetime><dax><powerquery>', 'Title': "How do I assess and create a conditional output where a rows' DateTime value sits between an earlier row's Start/End DateTime?", 'ViewCount': 52, 'id': 71485452, 'user_AboutMe': '', 'user_AccountId': 4172349.0, 'user_DisplayName': 'cptcherry', 'user_Reputation': 33.0, 'user_Views': 17.0, 'user_id': 3419830.0}}