Fill in Missing End Dates in Table

[Anonymous]

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. 

ID	Start Date	End Date	Product Step	Order	Adjusted End Date
8054	10/12/2018	10/16/2018	Line 3	2	10/16/2018
8054	10/7/2018	10/12/2018	Line 2	1	10/12/2018
8054	10/5/2018		Line 1	0	10/7/2018

Thanks!

[Kristjan76 · Accepted solution]

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

[Anonymous]

Thanks! Worked perfectly. 

[Kristjan76]

Thats great, how about thumbs up for that 😉

[Anonymous]

this link may be of some help

https://community.powerbi.com/t5/Desktop/Combine-two-Tables-Actual-and-Forecast-into-third-table/m-p...