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

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