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

  • fhill's avatar
    fhill
    Resident Rockstar
    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

    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

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