Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
I need to calculate the avg time taken by the docs at each level i.e. how much time it takes for an avg doc to clear the draft stage or In-approval level 1 stage.
I have different docs which have unique levels for each status (according to the process) and timestamp of how much time it took on each status-level.
In text format:-
Doc No. | Status | Level | Timestmap |
1 | Draft | 1 | 10/19/2022 , 02:00:43 PM |
1 | In-approval | 1 | 10/20/2022, 04:00:43 PM |
1 | In-approval | 2 | 10/21/2022, 08:00:43 PM |
1 | In-approval | 3 | 10/23/2022, 12:10:43 PM |
1 | In-approval | 4 | 10/23/2022, 12:55:43 PM |
1 | Approved | 1 | 10/24/2022, 01:20:23 PM |
1 | Approved | 2 | 10/25/2022, 02:35:13 PM |
1 | Approved | 3 | 10/26/2022, 04:45:33 PM |
1 | Signature | 1 | 10/27/2022, 08:53:41 AM |
1 | Signature | 2 | 10/27/2022, 08:53:41 PM |
1 | Signature verification | 1 | 10/29/2022, 12:55:43 PM |
1 | Authorized | 1 | 10/30/2022, 12:55:43 PM |
2 | Draft | 1 | 10/19/2022 , 02:22:13 PM |
2 | In-approval | 1 | 10/20/2022, 04:22:23 PM |
2 | In-approval | 2 | 10/21/2022, 12:00:33 PM |
2 | In-approval | 3 | 10/23/2022, 12:42:21 PM |
2 | Approved | 1 | 10/24/2022, 01:20:23 PM |
2 | Approved | 2 | 10/24/2022, 03:20:23 PM |
2 | Approved | 3 | 10/25/2022, 02:35:13 PM |
2 | Approved | 4 | 10/26/2022, 04:45:33 PM |
2 | Signature | 1 | 10/27/2022, 08:53:41 AM |
2 | Signature verification | 1 | 10/29/2022, 12:55:43 PM |
2 | Signature verification | 2 | 10/29/2022, 08:25:26 PM |
2 | Authorized | 1 | 10/30/2022, 05:30:00 PM |
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) We can create calculated columns.
Rank by No =
RANKX(FILTER('Data table','Data table'[Doc No.]=EARLIER('Data table'[Doc No.])),'Data table'[Timestmap],,ASC,Dense)
Duration =
var _lasttimestamp=MAXX(FILTER('Data table','Data table'[Doc No.]=EARLIER('Data table'[Doc No.])&&[Rank by No]=EARLIER('Data table'[Rank by No])+1),'Data table'[Timestmap])
return
DATEDIFF('Data table'[Timestmap],_lasttimestamp,MINUTE)
(2)Then we can create a table.
Expected output =
SUMMARIZE('Data table','Data table'[Doc No.],'Data table'[Level],"Duration(Hours)",AVERAGE('Data table'[Duration])/60)
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) We can create calculated columns.
Rank by No =
RANKX(FILTER('Data table','Data table'[Doc No.]=EARLIER('Data table'[Doc No.])),'Data table'[Timestmap],,ASC,Dense)
Duration =
var _lasttimestamp=MAXX(FILTER('Data table','Data table'[Doc No.]=EARLIER('Data table'[Doc No.])&&[Rank by No]=EARLIER('Data table'[Rank by No])+1),'Data table'[Timestmap])
return
DATEDIFF('Data table'[Timestmap],_lasttimestamp,MINUTE)
(2)Then we can create a table.
Expected output =
SUMMARIZE('Data table','Data table'[Doc No.],'Data table'[Level],"Duration(Hours)",AVERAGE('Data table'[Duration])/60)
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
12 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
27 | |
19 | |
14 | |
11 | |
7 |