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

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

Reply
manubk
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 

 

manubk_2-1709642035267.png

 

@community support

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@manubk 

you can try this to get the time

Column = minx(FILTER('Table','Table'[start_time]>EARLIER('Table'[start_time])),'Table'[start_time])
11.PNG
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!




View solution in original post

v-nuoc-msft
Community Support
Community Support

Hi @manubk 

 

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

vnuocmsft_0-1709706510498.png

 

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.

vnuocmsft_1-1709706768213.png

 

Regards,

Nono Chen

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

 

 

View solution in original post

6 REPLIES 6
manubk
Frequent Visitor

@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

 

manubk_0-1710413882664.png

 

 

v-nuoc-msft
Community Support
Community Support

Hi @manubk 

 

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

vnuocmsft_0-1709706510498.png

 

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.

vnuocmsft_1-1709706768213.png

 

Regards,

Nono Chen

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

 

 

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 

 

manubk_0-1709723846489.png

 

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!




@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

 

manubk_0-1710245358987.png

 

ryan_mayu
Super User
Super User

@manubk 

you can try this to get the time

Column = minx(FILTER('Table','Table'[start_time]>EARLIER('Table'[start_time])),'Table'[start_time])
11.PNG
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!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.