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

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

  • andrewhann's avatar
    andrewhann
    Helper II
    8 years ago

    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

     

    • andrewhann's avatar
      andrewhann
      Helper II
      8 years ago

      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

    • fhill's avatar
      fhill
      Resident Rockstar
      8 years ago





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

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