cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## How to get the Cross dates Duration based on other columns

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

@community support

2 ACCEPTED SOLUTIONS
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Community Support

Hi @manubk

@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.

6 REPLIES 6
Frequent Visitor

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

Community Support

Hi @manubk

@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.

Frequent Visitor

Hi @v-nuoc-msft @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

Super User

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@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

Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors