Forum Discussion

sushmitasur4's avatar
sushmitasur4
Regular Visitor
2 years ago
Solved

Calculating Time Difference between Start and End Timestamps excluding overlapping of multiple rows

Explanation:   We need a calculated Column/Measure that can calculate the time difference excluding the overlapping time between two Timestamp Columns: Start Time and End Time.       Each...
Need Help
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    2 years ago

    sushmitasur4 , First All will two datetime column 

     

    Start Datetime = [Start Date] + [Start Time]

     

    End Datetime = [End Date] + [End Time]

     

    Now will create a new end time

    New end datetime =

    var _min = minx(filter(Table, Table, [Start DateTime] >= Earlier([Start DateTime]) && [Start DateTime] <= Earlier([End DateTime]) && [Event] > earlier([Event]) ), [Start DateTime])

    return

    if(isblank(_min, [End Datime], _min)

     

    Then you can use date diff of start datetime with the New end datetime

amitchandak's avatar
amitchandak
Super User
2 years ago

sushmitasur4 , First All will two datetime column 

 

Start Datetime = [Start Date] + [Start Time]

 

End Datetime = [End Date] + [End Time]

 

Now will create a new end time

New end datetime =

var _min = minx(filter(Table, Table, [Start DateTime] >= Earlier([Start DateTime]) && [Start DateTime] <= Earlier([End DateTime]) && [Event] > earlier([Event]) ), [Start DateTime])

return

if(isblank(_min, [End Datime], _min)

 

Then you can use date diff of start datetime with the New end datetime