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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Joaomatos2002
Frequent Visitor

detecting overlapping activities with time and date

Hello everyone, I hope you are well!

 

I'm having a problem trying to check for overlapping activities in a table I'm using. I found several posts similar to my situation, but I can't solve it. If anyone can help, I would appreciate it.


my TABLE:

| ID_Card   | ID_Act              Inicio                         | Fim                             |  DATe                 | chEckOVERLAPPING
|-----------|-----------------|-----------------------|-------------------------|------------------|---------------------------|
| 1             | 101                  | 2024-08-31 23:00:00 | 2024-08-01 10:00:00 |  2024-08-01      |  
| 2             | 102                  | 2024-08-01 09:00:00 | 2024-08-01 19:00:00 |  2024-08-01      |   
| 1             | 103                  | 2024-08-01 12:00:00 | 2024-08-01 13:00:00 |  2024-08-01      |    

| 2             | 104                  | 2024-08-01 12:00:00 | 2024-08-01 18:00:00 |  2024-08-01      |    

| 2             | 105                  | 2024-08-01 19:30:00 | 2024-08-01 20:00:00 |  2024-08-01      |    

| 3             | 106                  | 2024-08-01 14:00:00 | 2024-08-01 18:00:00 |   2024-08-01     |  

| 3             | 107                 | 2024-08-01 17:00:00 | 2024-08-01 21:00:00 |   2024-08-01      |    

 

RESULT PRETEND: 
| ID_Card   | ID_Act              Inicio                         | Fim                             |  DATe                 | chEckOVERLAPPING
|-----------|-----------------|-----------------------|-------------------------|------------------|---------------------------|
| 1             | 101                  | 2024-08-31 23:00:00 | 2024-08-01 10:00:00 |  2024-08-01      |   FALSE
| 2             | 102                  | 2024-08-01 09:00:00 | 2024-08-01 19:00:00 |  2024-08-01      |   TRUE
| 1             | 103                  | 2024-08-01 12:00:00 | 2024-08-01 13:00:00 |  2024-08-01      |    FALSE

| 2             | 104                  | 2024-08-01 12:00:00 | 2024-08-01 18:00:00 |  2024-08-01      |    TRUE

| 2             | 105                  | 2024-08-01 19:30:00 | 2024-08-01 20:00:00 |  2024-08-01      |    FALSE

| 3             | 106                  | 2024-08-01 14:00:00 | 2024-08-01 18:00:00 |   2024-08-01     |    TRUE

| 3             | 107                 | 2024-08-01 17:00:00 | 2024-08-01 21:00:00 |   2024-08-01      |    TRUE

 

 

whAT usER  SEE ON DASH :

 

ID_cARD =  2

daTE = 2024-08-01

 

| ID_Card   | ID_Act              Inicio                         | Fim                             |  DATe                 | chEckOVERLAPPING
|-----------|-----------------|-----------------------|-------------------------|------------------|---------------------------|
| 2             | 102                  | 2024-08-01 09:00:00 | 2024-08-01 19:00:00 |  2024-08-01      |   TRUE

| 2             | 104                  | 2024-08-01 12:00:00 | 2024-08-01 18:00:00 |  2024-08-01      |    TRUE

| 2             | 105                  | 2024-08-01 19:30:00 | 2024-08-01 20:00:00 |  2024-08-01      |    FALSE

 

 

The table contains many records with various activities and card IDs.

The date refers to the end of the activity.

I remind you that the verification must be carried out only within the same card ID, as the data is displayed in a table filtered by the ID_CARD. As I have already mentioned, I have checked several posts in the community on this subject, but errors always occur. Thank you for your attention.

 

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @Joaomatos2002, check this:

 

Comment: there is a mistake in your sample data (first row). There should be [Inicio] 2024-07-31 23:00:00 in my opinion.

 

Remove these 3 steps if you don't need same row order:

dufoq3_0-1722961276224.png

 

Result

dufoq3_0-1722960961856.png

 

v1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),

    Fn_CheckOverlaping = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[ID_Card=2]}[All],
            _Detail = myTable,
            _AddedIndex = Table.AddIndexColumn(_Detail, "IndexHelper", 0, 1, Int64.Type),
            _Zipped = List.Zip({ _AddedIndex[Inicio], _AddedIndex[Fim] }),
            _StepBack = _AddedIndex,
            _Ad_CheckOverlaping = Table.AddColumn(_StepBack, "Check Overlapping", each 
                [ a = List.Buffer(List.RemoveRange(_Zipped, [IndexHelper])),               //Removed current row from _Zipped
                  b = List.AnyTrue(List.Transform(a, (x)=> 
                                        if [Inicio] >= x{0} and [Inicio] < x{1} then true  //Inicio check
                                        else if [Fim] > x{0} and [Fim] <= x{1} then true   //Fim check
                                        else if [Inicio] < x{0} and [Fim] > x{1} then true //Outside Check
                                        else false) )
                ][b], type logical),
            _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlaping, {"IndexHelper"})
        ][_RemovedColumns],

    GroupedRows = Table.Group(AddedIndex, {"ID_Card"}, {{"Fn", Fn_CheckOverlaping, type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn]),
    SortedRows = Table.Sort(CombinedFn,{{"Index", Order.Ascending}}),
    RemovedIndex = Table.RemoveColumns(SortedRows,{"Index"}, MissingField.Ignore)
in
    RemovedIndex

 

 

v2 (this one should be faster)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}),
    AddedIndexHelper = Table.AddIndexColumn(ChangedType, "IndexHelper", 0, 1, Int64.Type),

    fn_CheckOverlapping = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[ID_Card=2]}[Fn],
            _Detail = myTable,
            _Ad_DateTimes = Table.AddColumn(_Detail, "DateTimes", each { [IndexHelper], List.DateTimes([Inicio], Duration.TotalMinutes([Fim]-[Inicio])+1, #duration(0,0,1,0)) }, type list),
            _DateTimes = _Ad_DateTimes[DateTimes],
            _StepBack = _Ad_DateTimes,
            _Ad_CheckOverlapping = Table.AddColumn(_StepBack, "Check Overlapping", each 
                [ a = List.Select(_DateTimes, (x)=> x{0} <> [IndexHelper]),
                b = List.Combine(List.Transform(a, (x)=> x{1})),
                c = if List.ContainsAny(b, [DateTimes]{1}) then true else false
                ][c], type logical),
            _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlapping,{"IndexHelper", "DateTimes"})
        ][_RemovedColumns],

    GroupedRows = Table.Group(AddedIndexHelper, {"ID_Card"}, {{"Fn", fn_CheckOverlapping, type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn])
in
    CombinedFn

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

8 REPLIES 8
dufoq3
Super User
Super User

Hi @Joaomatos2002, check this:

 

Comment: there is a mistake in your sample data (first row). There should be [Inicio] 2024-07-31 23:00:00 in my opinion.

 

Remove these 3 steps if you don't need same row order:

dufoq3_0-1722961276224.png

 

Result

dufoq3_0-1722960961856.png

 

v1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),

    Fn_CheckOverlaping = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[ID_Card=2]}[All],
            _Detail = myTable,
            _AddedIndex = Table.AddIndexColumn(_Detail, "IndexHelper", 0, 1, Int64.Type),
            _Zipped = List.Zip({ _AddedIndex[Inicio], _AddedIndex[Fim] }),
            _StepBack = _AddedIndex,
            _Ad_CheckOverlaping = Table.AddColumn(_StepBack, "Check Overlapping", each 
                [ a = List.Buffer(List.RemoveRange(_Zipped, [IndexHelper])),               //Removed current row from _Zipped
                  b = List.AnyTrue(List.Transform(a, (x)=> 
                                        if [Inicio] >= x{0} and [Inicio] < x{1} then true  //Inicio check
                                        else if [Fim] > x{0} and [Fim] <= x{1} then true   //Fim check
                                        else if [Inicio] < x{0} and [Fim] > x{1} then true //Outside Check
                                        else false) )
                ][b], type logical),
            _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlaping, {"IndexHelper"})
        ][_RemovedColumns],

    GroupedRows = Table.Group(AddedIndex, {"ID_Card"}, {{"Fn", Fn_CheckOverlaping, type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn]),
    SortedRows = Table.Sort(CombinedFn,{{"Index", Order.Ascending}}),
    RemovedIndex = Table.RemoveColumns(SortedRows,{"Index"}, MissingField.Ignore)
in
    RemovedIndex

 

 

v2 (this one should be faster)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}),
    AddedIndexHelper = Table.AddIndexColumn(ChangedType, "IndexHelper", 0, 1, Int64.Type),

    fn_CheckOverlapping = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[ID_Card=2]}[Fn],
            _Detail = myTable,
            _Ad_DateTimes = Table.AddColumn(_Detail, "DateTimes", each { [IndexHelper], List.DateTimes([Inicio], Duration.TotalMinutes([Fim]-[Inicio])+1, #duration(0,0,1,0)) }, type list),
            _DateTimes = _Ad_DateTimes[DateTimes],
            _StepBack = _Ad_DateTimes,
            _Ad_CheckOverlapping = Table.AddColumn(_StepBack, "Check Overlapping", each 
                [ a = List.Select(_DateTimes, (x)=> x{0} <> [IndexHelper]),
                b = List.Combine(List.Transform(a, (x)=> x{1})),
                c = if List.ContainsAny(b, [DateTimes]{1}) then true else false
                ][c], type logical),
            _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlapping,{"IndexHelper", "DateTimes"})
        ][_RemovedColumns],

    GroupedRows = Table.Group(AddedIndexHelper, {"ID_Card"}, {{"Fn", fn_CheckOverlapping, type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn])
in
    CombinedFn

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you for your effort and time, I thank you from the bottom of my heart. Its working now. You had already succeeded, but what you did was what you originally wanted.

I also leave here a code that I found in this community: 

Time_Check_OverLap =
VAR _Person = 'YourTable'[PersonID] -- Variable to hold the current person ID
VAR _start = 'YourTable'[StartTime] -- Variable to hold the start time of the current record
VAR _end = 'YourTable'[EndTime] -- Variable to hold the end time of the current record

-- Variable to calculate the overlap condition
VAR _condition_overlap =
CALCULATE(
COUNT('YourTable'[PersonID]), -- Counting the number of records with the same person ID that meet the overlap conditions
FILTER(
'YourTable',
'YourTable'[PersonID] = _Person -- Ensuring the same person ID
&& (
(_start < 'YourTable'[StartTime] && _end > 'YourTable'[EndTime]) -- Case 1: Current record fully overlaps another record
|| _start = 'YourTable'[StartTime] -- Case 2: Current record starts at the same time as another record
|| (_start > 'YourTable'[StartTime] && _start < 'YourTable'[EndTime]) -- Case 3: Current record starts within the duration of another record
)
)
)

-- Returning "n" if there is no overlap or only one occurrence (itself), otherwise returning "s"
RETURN IF(_condition_overlap <= 1, "n", "s")

 

!  P E A C E    F A M  !

(͡• ͜ʖ ͡•)

I've added v2 into my previous post. V2 should be faster.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

p45cal
Resolver II
Resolver II

In the linked-to Excel workbook below at the end, I have your source data in the blue table and the result table in green:

 

p45cal_0-1722938136852.png

 

You will see the result in the chEckOVERLAPPING column matches your expected results.

Note that the first row of your dates Inicio is after Fim!

I've added a bonus column on the right of the green table to show which Act_IDs that row overlaps with.

Outside of the blue source table, below it, I've added an extra row to demonstrate what happens when multiple overlaps occur; just extend the table to include that row and refresh the green table.

You get:

p45cal_1-1722938472678.png

The M-code could be more elegant and have fewer steps, but you'll see that it also calculates how much overlap there is in each case.

If I get time, I'll post the whole M-code including the compressed source data…

Anyway, the workbook link: https://app.box.com/s/33409zxzelv3o40o0qjhe033piakz911

 

Thank you for your effort, I thank you from the bottom of my heart. The idea of the bonus column to show the IDs of the activities that are overlapping is amazing. How to do it?

 

!  P E A C E    F A M  !

(͡• ͜ʖ ͡•)

lbendlin
Super User
Super User

Does it need to be in Power Query or can it be in DAX?  In either case you can use INTERSECT (or its equivalent) for this.

Hey, thanks for the reply!!!

I ended up succeeding, I used in desperation a code that I had already used, and guess what, this time it worked. Thank you for your time!

 

!  P E A C E    F A M  !

(͡• ͜ʖ ͡•)

Hi @Joaomatos2002 ,

Glad to hear that luck is on your side!
Please take the trouble to reply with your code below and mark your reply as solution so that more users facing the same or similar problem can find a solution faster and for other forum users to learn from. Thank you!

Best Regards,
Dino Tao

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.