Forum Discussion
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
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
7 Replies
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/339586
I cant quite make this fit with my example above,
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
Fantastic,
I will try this later on today and let you know how I get on
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
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
