cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem
Post Prodigy
Post Prodigy

calculating duration between two cells based on matching criteria

Bit of a complex one...

 

I have a time/date column called [Date Created] and a [Username] column, I want to calculate the duration between the first and next times - when the [Username] is the same, and the date of the [Date Created] field is the same. So my new column called [New Start Time] should populate the next [Date Created] against each row when the user is the same, and the date is the same, else it is left blank.

 

Before I created my calculated column, I sorted the [Date Created] ascending and added an [Index] column. Because I want it to check the username AND the date are the same, I also added a [Weekday] column.

 

My calculated column is below, and it works inconsistently which means sometimes it's populating the next [Date Created] when it's a different day, thus the duration between is 3,000 minutes instead of about 20 minutes. And sometimes it ignores the rule and leaves a blank [New Start Time] when the username and day is the same. So, I think my measure is wrong, and i'm hoping someone can help me!!

 

New Start Time = IF(AND([Username] = LOOKUPVALUE([Username],[Index],[Index]+1), LOOKUPVALUE([Weekday],[Index],[Index]+1)),
LOOKUPVALUE([Date Created],[Index],[Index]+1),BLANK()) 

 

1 ACCEPTED SOLUTION

@heytherejem 

is this what you want?

New Start Time = MINX(FILTER('Table','Table'[Username ]=EARLIER('Table'[Username ])&&'Table'[ Weekday ]=EARLIER('Table'[ Weekday ])&&'Table'[Date Created]>EARLIER('Table'[Date Created])),'Table'[Date Created])

1.PNG





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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
v-caitlyn-mstf
Community Support
Community Support

Hi @heytherejem ,

 

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

 

Hope it helps,


Community Support Team _ Caitlyn

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

heytherejem
Post Prodigy
Post Prodigy

Don't worry I ended up splitting the data so it was one user per list and it works fine. 

ryan_mayu
Super User
Super User

@heytherejem 

could you pls provide some sample data and expected output?





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

Proud to be a Super User!




@ryan_mayu 

 

The expected result would look something like the below. But what's happening at the moment is inconsistent where the blanks are or should be. 

 

Date Created                  Username     Weekday      Index        New Start Time

03/01/2020  09:49:26     Abs                6                  1               03/01/2020 10:07:05

03/01/2020  10:07:05     Abs                6                  2               03/01/2020 10:35:22    

03/01/2020  10:35:22     Abs                6                  3                 

03/01/2020  16:10:51    Tod                 6                  4               03/01/2020 16:16:47

03/01/2020  16:16:47    Tod                 6                  5                

06/01/2020  09:50:30    Tod                2                   6               06/01/2020 10:16:23

06/01/2020 10:16:23    Tod                 2                  7               06/01/2020 10:27:15

06/01/2020 10:27:15    Tod                 2                  8               

06/01/2020 10:37:13    Del                 2                  9               06/01/2020 10:58:21

06/01/2020 10:58:21    Del                 2                  10               

@heytherejem 

is this what you want?

New Start Time = MINX(FILTER('Table','Table'[Username ]=EARLIER('Table'[Username ])&&'Table'[ Weekday ]=EARLIER('Table'[ Weekday ])&&'Table'[Date Created]>EARLIER('Table'[Date Created])),'Table'[Date Created])

1.PNG





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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.