cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Get time between dates on seperate rows

I have a dataset eg

 

Date                              In     Out

12/01/2019 10:15                 10        

12/01/2019 10:20         10        

12/01/2019 10:35                 30        

12/01/2019 10:40                 50        

12/01/2019 11:05         10        

12/01/2019 11:40                 90        

12/01/2019 12:15                 10        

 

I need to get the duration of time between the Out columns. So for row 1 and 3 the time between is 20 mins

 

I dont have a row number in the data set soI need to work off date and time for the order

 

Any help would be really appreciated 

 

1 ACCEPTED SOLUTION
Community Support
Community Support

hi, @DebbieE

After my test, you could try this way:

Step1:

Use RANKX Function to add a row number for by [In] and [Out] column like this

In Rank = IF(Table1[In]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[In])=FALSE()),Table1[Date],,ASC ))
Out Rank = IF(Table1[Out]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[Out])=FALSE()),Table1[Date],,ASC ))

or combine them into one column

Rank = IF(Table1[In]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[In])=FALSE()),Table1[Date],,ASC ),IF(Table1[Out]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[Out])=FALSE()),Table1[Date],,ASC )))

2.JPG

Step2:

Use EARLIER Function to create a result column

In duration = DATEDIFF(CALCULATE(MIN(Table1[Date]),FILTER(Table1,Table1[In]<>BLANK()&&Table1[In Rank]=EARLIER(Table1[In Rank])-1)),Table1[Date],MINUTE)
Out duration = DATEDIFF(CALCULATE(MIN(Table1[Date]),FILTER(Table1,Table1[Out]<>BLANK()&&Table1[Out Rank]=EARLIER(Table1[Out Rank])-1)),Table1[Date],MINUTE)

3.JPG

 

 

here is pbix file, please try it.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
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

7 REPLIES 7
Community Support
Community Support

hi, @DebbieE

After my test, you could try this way:

Step1:

Use RANKX Function to add a row number for by [In] and [Out] column like this

In Rank = IF(Table1[In]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[In])=FALSE()),Table1[Date],,ASC ))
Out Rank = IF(Table1[Out]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[Out])=FALSE()),Table1[Date],,ASC ))

or combine them into one column

Rank = IF(Table1[In]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[In])=FALSE()),Table1[Date],,ASC ),IF(Table1[Out]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[Out])=FALSE()),Table1[Date],,ASC )))

2.JPG

Step2:

Use EARLIER Function to create a result column

In duration = DATEDIFF(CALCULATE(MIN(Table1[Date]),FILTER(Table1,Table1[In]<>BLANK()&&Table1[In Rank]=EARLIER(Table1[In Rank])-1)),Table1[Date],MINUTE)
Out duration = DATEDIFF(CALCULATE(MIN(Table1[Date]),FILTER(Table1,Table1[Out]<>BLANK()&&Table1[Out Rank]=EARLIER(Table1[Out Rank])-1)),Table1[Date],MINUTE)

3.JPG

 

 

here is pbix file, please try it.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
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

Can I confirm for Step 1

 

Are they calculated columns or measures? When I created them I could only make them as calculated measures

 

Confirmed myself. They are calculated measures

 

In Rank = IF(Table1[In]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[In])=FALSE()),Table1[Date],,ASC))
Out Rank = IF(Table1[Out]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[Out])=FALSE()),Table1[Date],,ASC))
Rank = IF(Table1[In]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[In])=FALSE()),Table1[Date],,ASC ),IF(Table1[Out]<>BLANK(),RANKX(FILTER(Table1,ISBLANK(Table1[Out])=FALSE()),Table1[Date],,ASC )))

Rankingissue.GIF

 

Mine looks different to the one above with the rankings. I know what happened here. This was because Id set my in and out to text rather than integers. Reset to numbers and it was all fine. However, why would the ranking work like the above image on text?

 

Ill carry on

hi, @DebbieE

First, They are calculated columns.

Second, However, why would the ranking work like the above image on text?

I need to get the duration of time between the Out columns. So for row 1 and 3 the time between is 20 mins

This is used to determine row number for each DateTime by In/Out. 

You could see the detail in my demo pbix file above.

 

Best Regards,

Lin

 

 

 

 

 

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

All working nicely 

 

Thank you so much

Fantastic,

 

I will try this later on today and let you know how I get on

Super User IV
Super User IV

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




I cant quite make this fit with my example above,

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors