Forum Discussion

andrewhann's avatar
andrewhann
Icon for Helper II rankHelper 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
Icon for Helper II rankHelper 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

 

fhill's avatar
fhill
Icon for Resident Rockstar rankResident 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
    Icon for Helper II rankHelper 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
    Icon for Resident Rockstar rankResident 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
    Icon for Helper II rankHelper 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
    Icon for Resident Rockstar rankResident 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.