Forum Discussion
hi
Hi,
I have following sample where i have created_at, status and order_code
for few order I have Confirmed, Delivered, On Process,Shipped on same day
but for few order when get after 8:00:00 PM then deliver that to next day after 8:00:00 AM (so no Confirmed, On Process, Shipped till next day)
I want to find Order time and average order time
from order Order Placed to On Confirmed ,
from order Confirmed to On process,
from On process to Shipped,
from Shipped to Delivered,
so
1) i need to seperate calculation orders that are placed after 8:00:00 PM and before 8:00:00 AM (this time will affect to my average calculation)
| order_code | status | created_at |
| 231101101451663 | Order placed | 01-11-23 08:16:00 PM |
| 231101101451663 | Confirmed | 02-11-23 10:35:00 AM |
| 231101101451663 | Delivered | 02-11-23 11:36:00 AM |
| 231101101451663 | On Process | 02-11-23 10:36:00 AM |
| 231101101451663 | Shipped | 02-11-23 10:40:00 AM |
| 231101102553553 | Order placed | 04-11-23 08:01:00 PM |
| 231101102553553 | Confirmed | 05-11-23 10:29:00 AM |
| 231101102553553 | Delivered | 05-11-23 11:26:00 AM |
| 231101102553553 | On Process | 05-11-23 10:30:00 AM |
| 231101102553553 | Shipped | 05-11-23 10:32:00 AM |
2) I want to find same day time difference of Confirmed and shipped as per order_code from 8:00:00 AM to 8:00:00 PM
| order_code | status | created_at |
| 231101101451773 | Confirmed | 01-11-23 10:16:00 AM |
| 231101101451773 | Delivered | 01-11-23 13:06:00 AM |
| 231101101451773 | On Process | 01-11-23 10:36:00 AM |
| 231101101451773 | Shipped | 01-11-23 10:37:00 AM |
| 231101102553846 | Confirmed | 02-11-23 10:29:00 AM |
| 231101102553846 | Delivered | 02-11-23 13:26:00 AM |
| 231101102553846 | On Process | 02-11-23 10:30:00 AM |
| 231101102553846 | Shipped | 02-11-23 10:30:00 AM |
please help me out for these calculations
thanks
Hi abc_777
It is a new requirement, You can start a new post on the forum, and if my solution is helpful to you, please consider marking it as a solution.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
I have created another ticket, if you can help me as you have worked on this
https://community.fabric.microsoft.com/t5/Desktop/hi/m-p/3655583#M1195573
thx
9 Replies
Hi abc_777
You can create a measure
Measure = VAR _ADD = ADDCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [order_code] IN VALUES ( 'Table'[order_code] ) ), "Flag", VAR A = VALUE ( FORMAT ( [created_at], "YYYYMMDD" ) & "0800" ) VAR B = VALUE ( FORMAT ( [created_at], "YYYYMMDD" ) & "2000" ) VAR C = VALUE ( FORMAT ( [created_at], "YYYYMMDDHHMM" ) ) RETURN IF ( C >= A && C <= B, 1 ) ) VAR _filter = MAXX ( FILTER ( _ADD, [status] IN VALUES ( 'Table'[status] ) ), [Flag] ) RETURN IF ( _filter = 1, DATEDIFF ( MINX ( FILTER ( _ADD, [Flag] <> 0 ), [created_at] ), MAXX ( FILTER ( _ADD, [Flag] <> 0 ), [created_at] ), MINUTE ) / SUMX ( _ADD, [Flag] ) )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous
thanks for your reply and try but i want the time difference of each status and want to exclude 8:00:00 PM to 8:00:00 AM
I want to find time difference of Order time and average order time
from order Order Placed to On Confirmed ,
from order Confirmed to On process,
from On process to Shipped,
from Shipped to Delivered,
I hope you can help me out
thanks
Hi abc_777
I am confused that, based on your original information, you can to calculate time difference of Confirmed and shipped as per order_code from 8:00:00 AM to 8:00:00 PM and now you want to calculate time difference for each status, what actual output you want?
Best Regards!
Yolo Zhu