Most variants, days between two dates, days between first and last

[ALMOHANNADI]

Dears,
Need your help on analyzing a process steps, I want to create I guess Pivot Tables to achieve:

• Get most variants of workflow process (how many times followed the same steps for all variants).
• Get the days between two days within Pivot table (each step).
• Get the days difference between the first date per ID and the last date.

I have table with three columns:
ID Activity Timestamp
1 initiate 01/01/2019
1 endorse 03/01/2019
1 approve 05/01/2019
2 initiate 02/01/2019
2 rejected 06/01/2019
3 initiate 03/01/2019
3 returned 04/01/2019
3 initiate 06/01/2019
3 endorse 08/01/2019
3 approve 10/01/2019

 

[lrobbo314]

Well, it's not particularly pretty, but it seems to have everything you need.

Here's what I did.

I added the table to Power Query.
Added an Index column.
Loaded the table to connection only and checked the option to add the table to the data model.
In the data model, add a measure - MaxMinDiff:=MAX([Timestamp])-MIN([Timestamp])
Then add a column with the following formula...

=IF(

Table1[ID]=LOOKUPVALUE(Table1[ID],Table1[Index],Table1[Index]-1),

Table1[Timestamp]-LOOKUPVALUE(Table1[Timestamp],Table1[Index],Table1[Index]-1)

,0

)

Then in the data model, add a Pivot Table.

In the pivot table under Rows I have: ID-->Activity-->Timestamp

Under Values I have Activity as a count-->
The measure I created called MaxMinDiff-->
The column I created called DaysBetween as sum

Here's what the pivot table looks like.

Book1
	B	C	D	E
3	ID	StepCount	MaxMinDiff	Sum of DaysBetween
4	1	3	120	120
5	approve	1	0	61
6	5/1/2019	1	0	61
7	endorse	1	0	59
8	3/1/2019	1	0	59
9	initiate	1	0	0
10	1/1/2019	1	0	0
11	2	2	120	120
12	initiate	1	0	0
13	2/1/2019	1	0	0
14	rejected	1	0	120
15	6/1/2019	1	0	120
16	3	5	214	214
17	approve	1	0	61
18	10/1/2019	1	0	61
19	endorse	1	0	61
20	8/1/2019	1	0	61
21	initiate	2	92	61
22	3/1/2019	1	0	0
23	6/1/2019	1	0	61
24	returned	1	0	31
25	4/1/2019	1	0	31
26	Grand Total	10	273	454
Sheet7