cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
batmobile111
Regular Visitor

Calculation of average time between two levels

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 TypeLogged DateChanged DateOrder NumberLevelSub Order No
L22019-06-03T05:32:01Z08/08/2019 20:162000M1111
L22019-06-03T05:32:01Z08/08/2019 20:292000M1222
L22019-06-17T06:03:17Z23/07/2019 13:323000M1444
L22019-06-17T06:03:17Z31/07/2019 23:023000M1888
L32019-06-03T05:32:01Z05/06/2019 10:222000M1333
L32019-06-17T06:03:17Z21/06/2019 18:453000M1555
L22019-06-17T06:03:17Z16/08/2019 15:353000M1666
L32019-06-03T05:32:01Z25/06/2019 16:412000M10333
L32019-06-17T06:03:17Z22/07/2019 14:003000M10555
L22019-06-03T05:32:01Z12/08/2019 12:132000M10222
L22019-06-17T06:03:17Z20/08/2019 8:513000M10666
L22019-06-03T05:32:01Z08/08/2019 20:412000M4111
L22019-06-03T05:32:01Z08/08/2019 20:412000M4222
L22019-06-17T06:03:17Z24/07/2019 14:513000M4999
L22019-06-17T06:03:17Z01/08/2019 1:293000M4888
L32019-06-03T05:32:01Z05/06/2019 15:292000M4333
L32019-06-17T06:03:17Z24/06/2019 15:413000M4555
L22019-06-17T06:03:17Z16/08/2019 15:593000M4666
L22019-06-03T05:32:01Z08/08/2019 20:282000M2111
L22019-06-03T05:32:01Z08/08/2019 20:292000M2222
L22019-06-17T06:03:17Z24/07/2019 14:263000M2999
L22019-06-17T06:03:17Z31/07/2019 23:023000M2888
L32019-06-03T05:32:01Z05/06/2019 10:222000M2333
L32019-06-17T06:03:17Z21/06/2019 18:453000M2555
L22019-06-17T06:03:17Z16/08/2019 15:353000M2666
L22019-06-03T05:32:01Z12/08/2019 9:302000M6111
L22019-06-17T06:03:17Z24/07/2019 14:273000M6444
L22019-06-17T06:03:17Z25/07/2019 9:003000M6999
L22019-06-17T06:03:17Z02/08/2019 14:303000M6888

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.

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

Hi @batmobile111 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User III
Super User III

Hi @batmobile111 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors