How to calculate average time in minutes with 1 date column between multiple rows

[DeBIe]

Hi all,

I am not sure on how to approach this requirements. Hopefully you can help me.

The endresult should be a table with my users, total documents and the average time they spent updating these documents. Below is an example of my dataset. There should be a calculation for a user, taking the average time they spent between the first document they updated and the last. For example if a user updated 10 documents. 5 documents in 2 minutes and 5 documents in 6 minutes. The average would be 3 minutes for that user. Problem here is dat I do not have a start and enddate in the same record. 

DocumentGuid	UpdateDate	User
0349A07C-46A3-48AC-A201-C383BD786DA3	02/11/2022 05:39	a.al
0481F5E8-4791-4BC7-B3CA-2019A900F682	02/11/2022 06:54	a.ab
049B8F40-E73F-48EA-8C2E-747AA8B159F5	01/11/2022 23:34	a.al
04CA5B98-9E22-492E-93F0-E252EF12A140	02/11/2022 01:29	a.al
04EED5DE-2788-445C-92B5-684554E334D5	02/11/2022 03:55	a.al
04EED5DE-2788-445C-92B5-684554E334D5	02/11/2022 03:55	a.al
077A44B7-9799-4C3C-B85D-4DABC7BA7473	01/11/2022 23:29	a.al
08368D46-0789-4873-9D82-84247D6E8A5A	02/11/2022 07:27	a.ab
083C7468-D506-4C35-A51B-28C2D47BE577	02/11/2022 04:42	a.al
08960EAB-C5B4-4F72-ADEA-B1CB6C461706	02/11/2022 06:37	a.al
08FA49BF-790A-4201-BFDA-95754BDC6B5C	02/11/2022 03:59	a.al
08FAF260-A97F-49FF-8C6D-31F6F3378D3C	02/11/2022 02:27	a.al
091D1540-5FF1-4348-B495-6CBFF1023154	02/11/2022 09:58	a.ab
09337C44-8E19-4CFA-BBF7-401AFFE7BD90	02/11/2022 04:00	a.al
0A6E06F4-E204-44DA-996A-6B2E1DD9D62D	01/11/2022 23:56	a.al
0ACB1DAB-B9AF-4E31-BC9F-268E1433232E	02/11/2022 07:25	a.ab
0B6B9FFD-96D3-4BE5-9328-F50AEC68B5E0	02/11/2022 00:17	a.al
0C7B8D67-CC24-473D-B97D-EE7FE3C37E06	02/11/2022 09:24	a.ab
0CAAE553-2968-47B6-81F8-776F44E6F645	01/11/2022 23:47	a.al

Endresult

User	TotalDocuments	EarliestUpdateDate	LatestUpdateDate	AverageTimeInMinutes
A	100	10-11-2022 09:00	10-11-2022 17:00	5
B	50	10-11-2022 09:00	10-11-2022 20:00	30

Thank you very much for your help!

[ToddChitt]

Can you not simply type out two sample rows of incoming data for one user, then type out an output row for what that data should translate to?

For example:

Document GUID    Update Date              User

===========   ========              ====

ABCD                      11/1/2022 9:00 AM     A

EFGH                        11/1/2022 9:05 AM    A

OUTPUT:

??

[DeBIe · Accepted solution]

From the data delivery team i've now received a time in seconds per item. So now I'm able to calculate the average time. This requirement is no longer needed. Thanks for your time!

[ToddChitt]

YouDataIsHardToUnderstandBecauseThereAreNoSpaces.

Can you give us pseudo-data? Plus your End Result grid data does not really match with the incoming sample. Sample has ranges of 02/11/2022 , but the End Result shows 11-10-2022.

Also, are BOTH dates formatted as MM-DD-YYYY?

Is 02/11 February 11th, or November 2nd? 

What is the "a.al" at the end of the first row?

[DeBIe]

@ToddChitt thanks for looking into my problem.

Sorry for the inconvenience. When posting, it told me that the HTML code had been corrected and then it removed the layout I had. Result is that spaces are gone. Don't know what went wrong :(.

The end result data indeed does not match. It's an example to hopefuly explain the problem and situation that I have. The data is for a full year, but in the report the end user views it per day. 

-Table is indeed formatted as MM-DD-YYYY
-It's November 2nd
-A.al is the name of a user (Letter of first name, divided by '.' and then first 2 letters of last name). I quickly masked this data.

What's the best way to share an excel file here?

[amitchandak]

@DeBIe , Based on what I got

Averagex(Values(Table[User]), calculate(datediff(min(Table[Update date]), Max(Table[Update Date]) , Minute) ) )

[DeBIe]

Hi @amitchandak  thank you for your reply.

I've tried this measure, but it returns the total minutes from first(min) UpdateDate and the last(max) UpdateDate. It does not give me the average for some reason. Any idea why? Because the formula starts with AverageX