Forum Discussion
Between dates on previous rows to then exclude, include, or include the extension of time.
Hi all,
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.
| 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 |
4 Replies
Anonymous Not sure how to do that in Power Query although I am sure there is a way. In DAX, you could do this using the approach used by MTBF. In your case, grab the MAXX and MINX values for Start and End for all previous rows (might want an Index column). Then you could compare and decide include or exclude.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousGreg_Deckler Thanks Greg for your reply.
I've attempted to use what you've provided, but it's not quite there yet!
Column =VAR __Current = [end time]VAR __PreviousDate = MAXX(FILTER('detailedreportextracts__small',[start Time] < EARLIER([start Time] ) && [ClientN] = earlier([ClientN]) ),[start Time])var __Previous = MAXX(filter(detailedreportextracts__small,[start Time]= __PreviousDate),[end Time])return__Current - __PreviousThe 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.
Thanks for your help with this.
Is there a reason why you would use DAX instead of PowerQuery in this instance? (or any instances, really)
I've primarily used powerquery as it works within excel and powerbi. Since you posted I've since read that you can use Dax within PowerPivot.
Greg_Deckler Hi Greg,
I also noticed you mentioned MINX in your reply, but it wasn't in your suggested code.
Any chance you could take another look?
Edit: added the below after playing around for another hour or so without much luck.I've been working to pull apart the DAX queries as they stand so I can understand them better.
I'm not sure if its 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.btw, I came across your LinkedIn pulse articles, very very amusing!
Hi Anonymous ,
Based on your sample data, could you please share more details about it? For example:
How did you get the Decision column manually? by the current default Start Time sort or else?
In this table, I have only filtered Client1 and sort the table by the Start Time, but seems like the result is something strange.
For the row Start Time = "10/1/2022 2:00:00 PM", it is after its previous Start Time '10/1/2022 1:46:00 PM' but it is also after its previous End Time '10/1/2022 1:59:00 PM' so whether the decision of this row should be 'include' instead of 'exclude', or am I understand it wrong?
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').In addition, could you explain it in details combined with the above table that how to get the 'partial include' result for the last row?
Best Regards,
Community Support Team _ Yingjie Li
