Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andrewhann
Helper II
Helper II

Production Gap

Capture.PNG

 

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

1 ACCEPTED SOLUTION


The last two references need to be FirstBookDate.
MAXX = CALCULATE( MAXX(Table1,Table1[LASTBOOKDATE]), FILTER( ALL(Table1), Table1[PRODUCTION_LINE] = EARLIER(Table1[PRODUCTION_LINE]) && Table1[FIRSTBOOKDATE]< EARLIER(Table1[FIRSTBOOKDATE])))



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

15 REPLIES 15
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

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

 

Capture.PNG

 

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))

 

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




fhill
Resident Rockstar
Resident Rockstar

 

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)))

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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)))




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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

 

Capture.PNG

 

Regards

 






Double check the < sign before EARLIER, and post your MAXX code please.

Forrest
MAXX = CALCULATE( MAXX('20and30','20and30'[LASTBOOKDATE]), FILTER( ALL('20and30'), '20and30'[PRODUCTION_LINE] = EARLIER('20and30'[PRODUCTION_LINE]) && '20and30'[FIRSTBOOKDATE]< EARLIER('20and30'[LASTBOOKDATE])))



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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])))


The last two references need to be FirstBookDate.
MAXX = CALCULATE( MAXX(Table1,Table1[LASTBOOKDATE]), FILTER( ALL(Table1), Table1[PRODUCTION_LINE] = EARLIER(Table1[PRODUCTION_LINE]) && Table1[FIRSTBOOKDATE]< EARLIER(Table1[FIRSTBOOKDATE])))



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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

 

Capture.PNG

 

Thank you for your kind assistance so far

You have a bad LastBookDate that starts the group of errors... (EndDate exceeding the begin date of the next line.)
I would exclude this from the dataset since its erroneous. As long as the wrong value is in thelist of BookEnds that have to be compared to BookStarts, you'll get some form of error mathematically.



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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


Regards
Zubair

Please try my custom visuals

Please find attached sample data

 

WORDNO_PPSPRODUCT_CODEPRODUCTION_LINEFIRSTBOOKDATELASTBOOKDATE
706915412985M202/05/2017 13:4702/05/2017 13:49
706917412985M202/05/2017 14:4702/05/2017 15:05
708986346490L706/05/2017 00:2706/05/2017 01:31
708987346490L706/05/2017 08:1606/05/2017 08:18
709038471838L605/05/2017 11:4209/05/2017 13:11
709337392870M302/05/2017 15:2002/05/2017 18:18
709338392870M302/05/2017 19:1702/05/2017 22:36
709342392870M306/05/2017 13:0206/05/2017 13:45
709347427140L704/05/2017 04:4204/05/2017 04:46
709720193239L205/05/2017 11:0305/05/2017 13:14
709721193239L205/05/2017 10:4305/05/2017 10:56
709722193239L205/05/2017 12:3205/05/2017 14:32
709723193239L205/05/2017 14:4605/05/2017 15:32
709851392870M302/05/2017 11:1702/05/2017 15:06
709852392870M302/05/2017 22:4902/05/2017 23:42
709853392870M301/05/2017 22:3601/05/2017 23:19
709854392870M302/05/2017 07:5302/05/2017 07:55
709867211100T3003/05/2017 06:2403/05/2017 06:27
709868211100T3003/05/2017 12:2203/05/2017 13:08
709869211100T3003/05/2017 17:0003/05/2017 18:14
709890346340L802/05/2017 01:4402/05/2017 05:24
709893347320L405/05/2017 09:5805/05/2017 10:43
709905471322T3004/05/2017 01:2604/05/2017 01:42
709906471322T3004/05/2017 05:4504/05/2017 07:09
709907471322T3004/05/2017 11:0604/05/2017 12:47
709912471800T3002/05/2017 12:1302/05/2017 14:02
709913471800T3002/05/2017 19:0702/05/2017 19:51
709919471808L602/05/2017 15:0802/05/2017 16:30
709927471808L604/05/2017 16:3404/05/2017 19:50
709940473000L702/05/2017 05:1702/05/2017 10:47
709941473000L702/05/2017 12:2402/05/2017 18:09
710020194300L206/05/2017 10:3506/05/2017 10:37
710024197460L206/05/2017 11:2706/05/2017 11:46
710026218900L205/05/2017 17:3305/05/2017 18:18
710027218900L205/05/2017 18:3605/05/2017 19:22
710029297580M205/05/2017 00:1905/05/2017 01:47
710030297580M205/05/2017 01:4905/05/2017 02:41
710057382730L103/05/2017 07:5503/05/2017 08:38
710058382730L103/05/2017 10:2803/05/2017 10:54
710059382730L103/05/2017 11:1503/05/2017 11:51
710060382730L103/05/2017 13:2803/05/2017 14:27
710063391020L103/05/2017 21:2603/05/2017 21:27
710064391020L104/05/2017 07:5604/05/2017 08:14
710065391020L103/05/2017 19:2303/05/2017 19:24
710066391020L103/05/2017 15:5603/05/2017 15:58
710067391020L103/05/2017 18:2203/05/2017 18:24
710068391020L104/05/2017 00:0604/05/2017 00:51
710069391020L104/05/2017 02:0504/05/2017 02:25
710070391020L104/05/2017 05:0804/05/2017 05:48
710071391020L103/05/2017 23:0603/05/2017 23:48

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.