Calculation of average time between two levels

[Anonymous]

Hi Team,
Thanks for the help in the past.You guys are just awesome.
Could you please help me in this new issue.
I have my dataset as shown below :

Customer Type	Logged Date	Changed Date	Order Number	Level	Sub Order No
L2	2019-06-03T05:32:01Z	08/08/2019 20:16	2000	M1	111
L2	2019-06-03T05:32:01Z	08/08/2019 20:29	2000	M1	222
L2	2019-06-17T06:03:17Z	23/07/2019 13:32	3000	M1	444
L2	2019-06-17T06:03:17Z	31/07/2019 23:02	3000	M1	888
L3	2019-06-03T05:32:01Z	05/06/2019 10:22	2000	M1	333
L3	2019-06-17T06:03:17Z	21/06/2019 18:45	3000	M1	555
L2	2019-06-17T06:03:17Z	16/08/2019 15:35	3000	M1	666
L3	2019-06-03T05:32:01Z	25/06/2019 16:41	2000	M10	333
L3	2019-06-17T06:03:17Z	22/07/2019 14:00	3000	M10	555
L2	2019-06-03T05:32:01Z	12/08/2019 12:13	2000	M10	222
L2	2019-06-17T06:03:17Z	20/08/2019 8:51	3000	M10	666
L2	2019-06-03T05:32:01Z	08/08/2019 20:41	2000	M4	111
L2	2019-06-03T05:32:01Z	08/08/2019 20:41	2000	M4	222
L2	2019-06-17T06:03:17Z	24/07/2019 14:51	3000	M4	999
L2	2019-06-17T06:03:17Z	01/08/2019 1:29	3000	M4	888
L3	2019-06-03T05:32:01Z	05/06/2019 15:29	2000	M4	333
L3	2019-06-17T06:03:17Z	24/06/2019 15:41	3000	M4	555
L2	2019-06-17T06:03:17Z	16/08/2019 15:59	3000	M4	666
L2	2019-06-03T05:32:01Z	08/08/2019 20:28	2000	M2	111
L2	2019-06-03T05:32:01Z	08/08/2019 20:29	2000	M2	222
L2	2019-06-17T06:03:17Z	24/07/2019 14:26	3000	M2	999
L2	2019-06-17T06:03:17Z	31/07/2019 23:02	3000	M2	888
L3	2019-06-03T05:32:01Z	05/06/2019 10:22	2000	M2	333
L3	2019-06-17T06:03:17Z	21/06/2019 18:45	3000	M2	555
L2	2019-06-17T06:03:17Z	16/08/2019 15:35	3000	M2	666
L2	2019-06-03T05:32:01Z	12/08/2019 9:30	2000	M6	111
L2	2019-06-17T06:03:17Z	24/07/2019 14:27	3000	M6	444
L2	2019-06-17T06:03:17Z	25/07/2019 9:00	3000	M6	999
L2	2019-06-17T06:03:17Z	02/08/2019 14:30	3000	M6	888

Scenario is this is an orders table. Each order(e.g 2000)  includes multiple sub-orders(e.g 111,222 etc) as mentioned above.Similarly Order 3000 has placed multiple sub orders.
Each of these Sub orders travel through various levels (M1,M6,M2 etc) and users update these sub orders. The raised and changed date are updated as they move from one level to another.
Now I need to calculate
Point 1:
average time spent for all orders (keeping track of Sub orders too) in each of the level e.g
1)Average time spent by all orders  from Level 1(M1) to Level 3 (M3) or
2)Average time spent from Level 3(M3) to Level4(M4) or time average calculation between any two levels.

Point 2:
Average time spent by orders in each of the levels(M1, M2,LM etc) i.e If I select M1 in Slicer,it should give me an average time  for all the orders spent in M1 and respectively M2,M3 etc
I used the Earlier function for the second point and I guess it worked.
Any other fool proof solution would be appreciated.

Thanks in advance.

[edhans · Accepted solution]

Hi @Anonymous you are going to have to provide some expected output result examples. For example:

• "Average time spent by all orders  from Level 1(M1) to Level 3 (M3) " - I don't know what that means:
  • I don't see any M3 orders
  • Do you mean all orders in a group for M1, M2, M3?
  • Do you mean all orders in the M1-M2-M3 larger group?
• "Average time spent from Level 3(M3) to Level4(M4) or time average calculation between any two levels"
  • Again, no M3 orders.
  • Do you mean orders for the same customer or any customer, and same order or any order as it moves from M3 to M4?
  • How are you selecting the "any two levels?"

Remember, we don't know your data or business at all, so please be as explicit as possible. Except for missing M3 orders, your data looks really good, we just need clearification (or I do at least) on your expected output. You can do the math in Excel and post screenshots if you want as long as we can clearly follow your examples.

[Anonymous]

Hi @edhans  ,

Thanks a lot for your suggestions.I get your doubts.

Apologies for a buggy dataset and poor summary.

Let me get back with a better explanation on the problem.

I am marking this as close till then.

[edhans]

Sure @Anonymous , open a new thread when you are ready and we are all here to jump in. Have a great day!