Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Can anyone help with the following - I want to measure the time difference between the LASTBOOKDATE and FIRSTBOOKDATE from one Works Order to the next (ie Production Gaps) on each Production Line.
Works Orders will not run consecutively across Production Lines so for each line orders will need to be sorted by FIRSTBOOKDATE (ie Start Time) and the gap measured from the LASTBOOKDATE (ie Finish Time) of the previous record on that production line.
Simple in excel- not so much in Power BI
Solved! Go to Solution.
Proud to give back to the community!
Thank You!
Hi @andrewhann
is this the expected result?
One case
082959 911201A L1 TimeDifference = 28/12/2016 10:41:00 - 28/12/2016 09:37:00
Sorry - it would have helped if my SQL were correct - there should only be one line per Works Order
For consecutive orders (by FIRSTBOOKDATE) on the same production line I want the gap between last order finishing and this order starting
So in this case - 30/12/16 19:36 - 30/12/16 19:09 ie production gap of 27 minutes on L2
*** Getting Error with your updated sample code.... Working on it ****
Here are two Custom Columns (not measures) to help you with your request. The first Matches Lanes & Finds changes based on FirstBookDateTime (Not Work Order as you menteioned Work Orders can varry in Lanes).
The 2nd takes a DateDiff in Hours (or whatever timeframe you need) if the MAXX value isn't blank.
Thank You,
FOrrest
MAXX = CALCULATE( MAXX(Table1,Table1[LastBookDateTime])
, FILTER(ALL(Table1), Table1[Lane] = EARLIER(Table1[Lane]) && Table1[FirstBookDateTime] < EARLIER(Table1[FirstBookDateTime])))
Idle Time = IF( ISBLANK( Table1[MAXX] ),0, DATEDIFF(Table1[MAXX],Table1[FirstBookDateTime],HOUR))
Proud to give back to the community!
Thank You!
Here's the updated code based on your sample data:
MAXX = CALCULATE( MAXX(Table1,Table1[LASTBOOKDATE]), FILTER( ALL(Table1), Table1[PRODUCTION_LINE] = EARLIER(Table1[PRODUCTION_LINE]) && Table1[FIRSTBOOKDATE]< EARLIER(Table1[FIRSTBOOKDATE])))
Idle Time = IF (ISBLANK( Table1[MAXX]),0,
IF ( Table1[MAXX] = Table1[FIRSTBOOKDATE], 0,
DATEDIFF(Table1[MAXX],Table1[FIRSTBOOKDATE],HOUR)))
Proud to give back to the community!
Thank You!
My code as follows:
MAXX = CALCULATE( MAXX('20and30','20and30'[LASTBOOKDATE]), FILTER( ALL('20and30'), '20and30'[PRODUCTION_LINE] = EARLIER('20and30'[PRODUCTION_LINE]) && '20and30'[FIRSTBOOKDATE]< EARLIER('20and30'[LASTBOOKDATE])))
Idle Time = IF (ISBLANK('20and30'[MAXX]),0,
IF ('20and30'[MAXX] = '20and30'[FIRSTBOOKDATE], 0,
DATEDIFF('20and30'[MAXX],'20and30'[FIRSTBOOKDATE],MINUTE)))
Getting an error on Idle Time = "In DATEDIFF function, the start date cannot be greater than the end date"
Due to bad data I know - but unfortunately production line colleagues aren't always the best at recording start and finish times
The issue is missing dates and/or firstdates before previous lastdate due to bad bookings
Try making the code '>=' instead of '='..... That should make all errors 0 (if that's ok?)
FOrrest
Idle Time = IF (ISBLANK('20and30'[MAXX]),0,
IF ('20and30'[MAXX] >= '20and30'[FIRSTBOOKDATE], 0,
DATEDIFF('20and30'[MAXX],'20and30'[FIRSTBOOKDATE],MINUTE)))
Proud to give back to the community!
Thank You!
That does get rid of the error message - however MAXX is giving me the LASTBOOKDATE of the same line not the previous line so the calculation is not correct
Regards
Proud to give back to the community!
Thank You!
MAXX = CALCULATE( MAXX('20and30','20and30'[LASTBOOKDATE]), FILTER( ALL('20and30'), '20and30'[PRODUCTION_LINE] = EARLIER('20and30'[PRODUCTION_LINE]) && '20and30'[FIRSTBOOKDATE]< EARLIER('20and30'[LASTBOOKDATE])))
Proud to give back to the community!
Thank You!
We - actually you - are almost there
If I look at one Line for a whole month sorted by firstdate we have some repeated MAXX dates causing incorrect result
Thank you for your kind assistance so far
Proud to give back to the community!
Thank You!
I have also nearly got there by RANKX which I can use to get the correct end date - but my rank is coming out in reverse order
RANK = RANKX (
ALL ( '20and30' ),
RANKX ( ALL ( '20and30' ), '20and30'[PRODUCTION_LINE],, ASC )
+ DIVIDE (
RANKX ( ALL ( '20and30' ), '20and30'[FIRSTBOOKDATE],, ASC ),
( COUNTROWS ( ALL ( '20and30') ) + 1 )
)
)
I thought this would RANK by Production Line and then Date in ascending order - but seems to be doing the opposite and if I change to DESC it stops working.
I would also need to RANK by a third column LASTBOOKDATE
Hi @andrewhann
This seems very much doable using a measure.
Please post sample data in a table format so i can copy it and write and test the measure.
Else you can share the file
Please find attached sample data
WORDNO_PPS | PRODUCT_CODE | PRODUCTION_LINE | FIRSTBOOKDATE | LASTBOOKDATE |
706915 | 412985 | M2 | 02/05/2017 13:47 | 02/05/2017 13:49 |
706917 | 412985 | M2 | 02/05/2017 14:47 | 02/05/2017 15:05 |
708986 | 346490 | L7 | 06/05/2017 00:27 | 06/05/2017 01:31 |
708987 | 346490 | L7 | 06/05/2017 08:16 | 06/05/2017 08:18 |
709038 | 471838 | L6 | 05/05/2017 11:42 | 09/05/2017 13:11 |
709337 | 392870 | M3 | 02/05/2017 15:20 | 02/05/2017 18:18 |
709338 | 392870 | M3 | 02/05/2017 19:17 | 02/05/2017 22:36 |
709342 | 392870 | M3 | 06/05/2017 13:02 | 06/05/2017 13:45 |
709347 | 427140 | L7 | 04/05/2017 04:42 | 04/05/2017 04:46 |
709720 | 193239 | L2 | 05/05/2017 11:03 | 05/05/2017 13:14 |
709721 | 193239 | L2 | 05/05/2017 10:43 | 05/05/2017 10:56 |
709722 | 193239 | L2 | 05/05/2017 12:32 | 05/05/2017 14:32 |
709723 | 193239 | L2 | 05/05/2017 14:46 | 05/05/2017 15:32 |
709851 | 392870 | M3 | 02/05/2017 11:17 | 02/05/2017 15:06 |
709852 | 392870 | M3 | 02/05/2017 22:49 | 02/05/2017 23:42 |
709853 | 392870 | M3 | 01/05/2017 22:36 | 01/05/2017 23:19 |
709854 | 392870 | M3 | 02/05/2017 07:53 | 02/05/2017 07:55 |
709867 | 211100 | T30 | 03/05/2017 06:24 | 03/05/2017 06:27 |
709868 | 211100 | T30 | 03/05/2017 12:22 | 03/05/2017 13:08 |
709869 | 211100 | T30 | 03/05/2017 17:00 | 03/05/2017 18:14 |
709890 | 346340 | L8 | 02/05/2017 01:44 | 02/05/2017 05:24 |
709893 | 347320 | L4 | 05/05/2017 09:58 | 05/05/2017 10:43 |
709905 | 471322 | T30 | 04/05/2017 01:26 | 04/05/2017 01:42 |
709906 | 471322 | T30 | 04/05/2017 05:45 | 04/05/2017 07:09 |
709907 | 471322 | T30 | 04/05/2017 11:06 | 04/05/2017 12:47 |
709912 | 471800 | T30 | 02/05/2017 12:13 | 02/05/2017 14:02 |
709913 | 471800 | T30 | 02/05/2017 19:07 | 02/05/2017 19:51 |
709919 | 471808 | L6 | 02/05/2017 15:08 | 02/05/2017 16:30 |
709927 | 471808 | L6 | 04/05/2017 16:34 | 04/05/2017 19:50 |
709940 | 473000 | L7 | 02/05/2017 05:17 | 02/05/2017 10:47 |
709941 | 473000 | L7 | 02/05/2017 12:24 | 02/05/2017 18:09 |
710020 | 194300 | L2 | 06/05/2017 10:35 | 06/05/2017 10:37 |
710024 | 197460 | L2 | 06/05/2017 11:27 | 06/05/2017 11:46 |
710026 | 218900 | L2 | 05/05/2017 17:33 | 05/05/2017 18:18 |
710027 | 218900 | L2 | 05/05/2017 18:36 | 05/05/2017 19:22 |
710029 | 297580 | M2 | 05/05/2017 00:19 | 05/05/2017 01:47 |
710030 | 297580 | M2 | 05/05/2017 01:49 | 05/05/2017 02:41 |
710057 | 382730 | L1 | 03/05/2017 07:55 | 03/05/2017 08:38 |
710058 | 382730 | L1 | 03/05/2017 10:28 | 03/05/2017 10:54 |
710059 | 382730 | L1 | 03/05/2017 11:15 | 03/05/2017 11:51 |
710060 | 382730 | L1 | 03/05/2017 13:28 | 03/05/2017 14:27 |
710063 | 391020 | L1 | 03/05/2017 21:26 | 03/05/2017 21:27 |
710064 | 391020 | L1 | 04/05/2017 07:56 | 04/05/2017 08:14 |
710065 | 391020 | L1 | 03/05/2017 19:23 | 03/05/2017 19:24 |
710066 | 391020 | L1 | 03/05/2017 15:56 | 03/05/2017 15:58 |
710067 | 391020 | L1 | 03/05/2017 18:22 | 03/05/2017 18:24 |
710068 | 391020 | L1 | 04/05/2017 00:06 | 04/05/2017 00:51 |
710069 | 391020 | L1 | 04/05/2017 02:05 | 04/05/2017 02:25 |
710070 | 391020 | L1 | 04/05/2017 05:08 | 04/05/2017 05:48 |
710071 | 391020 | L1 | 03/05/2017 23:06 | 03/05/2017 23:48 |
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |