- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
??
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@DeBIe , Based on what I got
Averagex(Values(Table[User]), calculate(datediff(min(Table[Update date]), Max(Table[Update Date]) , Minute) ) )
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
08-26-2024 01:19 PM | |||
12-14-2023 11:09 PM | |||
Anonymous
| 01-22-2024 09:59 AM | ||
08-08-2024 07:49 AM | |||
06-29-2024 06:36 AM |
User | Count |
---|---|
113 | |
76 | |
55 | |
54 | |
43 |
User | Count |
---|---|
183 | |
120 | |
80 | |
67 | |
57 |