Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Fill in Missing End Dates in Table

I have a table where ever once in a while there is missing data on one of the date fields that calculates the production time of an item. Basically, I have a start date but sometimes I don't get an end date even though the product has gone on to the next line. 

 

So, what I'm trying to do is write a fomula that will give me the "Adjusted End Date" column shown in the table below. I have a column called "Order" that also shows the process order,  but cannot figure out how to pull the Start date from another row based on the next step in that order column. 

 

Note, I need to use that order column as the sequential order as sometimes a product goes through two lines on the same day so I need to reference the production order. 

 

IDStart DateEnd DateProduct StepOrderAdjusted End Date
805410/12/201810/16/2018Line 3210/16/2018
805410/7/201810/12/2018Line 2110/12/2018
805410/5/2018 Line 1010/7/2018

 

Thanks!

  • Anonymous's avatar
    Anonymous
    7 years ago

    Try this

    Adj Date =
    IF(
      ISBLANK(Table[End Date]),
      VAR id = Table[ID]
      VAR order = Table[Order]
      VAR tbl = 
        FILTER(
          Table,
          Table[ID] = id && Table[Order] > order
        )
      RETURN
      IF(COUNTROWS(tbl) > 0, MINX(tbl, [Start Date]))
       

4 Replies