Forum Discussion

andrewhann's avatar
andrewhann
Helper II
8 years ago
Solved

Production Gap

  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...
Need Help
  • fhill's avatar
    fhill
    8 years ago

    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])))
andrewhann's avatar
andrewhann
Helper II
8 years ago

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

fhill's avatar
fhill
Resident Rockstar
8 years ago

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

 

  • fhill's avatar
    fhill
    Resident Rockstar
    8 years ago

     

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

     

     

    • andrewhann's avatar
      andrewhann
      Helper II
      8 years ago

      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

      • fhill's avatar
        fhill
        Resident Rockstar
        8 years ago

         

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