How to get the Cross dates Duration based on other columns

[Anonymous]

Hi All,

I have data in the following format and I am trying to get the dates in the below format as i need to find the Mean time between Failures for my report. I need to subtract the duration from End time to Next line Start time . Example  As we can see on first row the end time is 1/5/2022 12:36:22 AM and need to find the duration from 1/10/2022 6:56:06 AM and the next same as follows like 1/10/2022 7:40:38 AM - 1/20/2022 1:06:37 AM . Could you please help on this 

@Anonymous support

[ryan_mayu · Accepted solution]

@Anonymous 

you can try this to get the time

Column = minx(FILTER('Table','Table'[start_time]>EARLIER('Table'[start_time])),'Table'[start_time])

then you can do the calculation to get the duration.

[Anonymous]

@v-nuoc-msft @Ashish_Mathur  I need to show the calculation as "If End Time is lesser than Next row  start Time it need to show me the other row which is higher than End Time .. Example - In below screenshot in Second row End Time is 1/15/2024 9:12:00 Am and Next start time is 1/14/2024 6:43:33 Pm .. As the 3rd row start time is lesser than 2nd row End time it show negative values .. Here i need calculation where it should capture 1/18/2024 9:17:37 AM in Second row Next start time ... Thank you sooo much for helping in advance

[Anonymous · Accepted solution]

Hi @Anonymous 

@ryan_mayu Your method is perfect! I have added to this method based on yours.

Create a measure. For continuous differences between two dates, you can choose to calculate either days or hours. It depends on your needs.

Duration = 
    var end_time = SELECTEDVALUE('Table'[end_time])
    var next_start_time = SELECTEDVALUE('Table'[Next start_time])
RETURN 
    DATEDIFF(end_time, next_start_time, DAY) --The day can be modified to your desired calculation.

Create a measure. Calculation of average values.

Average Duration = AVERAGEX('Table', [Duration])

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous @ryan_mayu  Thank you sooo Much for your reply .. This helped me .. But i have one more scenario where in - If End Time is greater than the next Start Time it should Nullify Next start time (Minus Values Should be Zero for Seconds) .. Below is the screen shot of the same as i am trying to convert that in to Seconds instead of Days 

[ryan_mayu]

based on my solution, you can use if when you calculate the duration

Column =if( minx(FILTER('Table','Table'[start_time]>EARLIER('Table'[start_time])),'Table'[start_time])>endtime,minx(FILTER('Table','Table'[start_time]>EARLIER('Table'[start_time])),'Table'[start_time]),0)

[Anonymous]

@ryan_mayu  Thank you for you response.. I need to show the calculation as "If End Time is lesser than Next row  start Time it need to show me the other row which is higher than End Time .. Example - In below screenshot in Second row End Time is 1/15/2024 9:12:00 Am and Next start time is 1/14/2024 6:43:33 Pm .. As the 3rd row start time is lesser than 2nd row End time it show negative values .. Here i need calculation where it should capture 1/18/2024 9:17:37 AM in Second row Next start time ... Thank you sooo much for helping in advance

[ryan_mayu · Accepted solution]

@Anonymous 

you can try this to get the time

Column = minx(FILTER('Table','Table'[start_time]>EARLIER('Table'[start_time])),'Table'[start_time])

then you can do the calculation to get the duration.