Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DeBIe
Post Partisan
Post Partisan

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

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. 

 

DocumentGuidUpdateDateUser
0349A07C-46A3-48AC-A201-C383BD786DA302/11/2022 05:39a.al
0481F5E8-4791-4BC7-B3CA-2019A900F68202/11/2022 06:54a.ab
049B8F40-E73F-48EA-8C2E-747AA8B159F501/11/2022 23:34a.al
04CA5B98-9E22-492E-93F0-E252EF12A14002/11/2022 01:29a.al
04EED5DE-2788-445C-92B5-684554E334D502/11/2022 03:55a.al
04EED5DE-2788-445C-92B5-684554E334D502/11/2022 03:55a.al
077A44B7-9799-4C3C-B85D-4DABC7BA747301/11/2022 23:29a.al
08368D46-0789-4873-9D82-84247D6E8A5A02/11/2022 07:27a.ab
083C7468-D506-4C35-A51B-28C2D47BE57702/11/2022 04:42a.al
08960EAB-C5B4-4F72-ADEA-B1CB6C46170602/11/2022 06:37a.al
08FA49BF-790A-4201-BFDA-95754BDC6B5C02/11/2022 03:59a.al
08FAF260-A97F-49FF-8C6D-31F6F3378D3C02/11/2022 02:27a.al
091D1540-5FF1-4348-B495-6CBFF102315402/11/2022 09:58a.ab
09337C44-8E19-4CFA-BBF7-401AFFE7BD9002/11/2022 04:00a.al
0A6E06F4-E204-44DA-996A-6B2E1DD9D62D01/11/2022 23:56a.al
0ACB1DAB-B9AF-4E31-BC9F-268E1433232E02/11/2022 07:25a.ab
0B6B9FFD-96D3-4BE5-9328-F50AEC68B5E002/11/2022 00:17a.al
0C7B8D67-CC24-473D-B97D-EE7FE3C37E0602/11/2022 09:24a.ab
0CAAE553-2968-47B6-81F8-776F44E6F64501/11/2022 23:47a.al

 

Endresult

 

UserTotalDocumentsEarliestUpdateDateLatestUpdateDateAverageTimeInMinutes
A10010-11-2022 09:0010-11-2022 17:005
B5010-11-2022 09:0010-11-2022 20:0030

 

Thank you very much for your help!

1 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!

View solution in original post

6 REPLIES 6
ToddChitt
Super User
Super User

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:

??

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
Super User
Super User

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?

@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
Super User
Super User

@DeBIe , Based on what I got

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.