Get time between dates on seperate rows

[DebbieE]

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 

[v-lili6-msft · Accepted solution]

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 )))

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)

here is pbix file, please try it.

Best Regards,

Lin

[DebbieE]

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 )))

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

[v-lili6-msft]

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

[DebbieE]

All working nicely 

Thank you so much

[DebbieE]

Fantastic,

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

[Greg_Deckler]

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

[DebbieE]

I cant quite make this fit with my example above,