Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
Kristjan76
Responsive Resident
Responsive Resident

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

View solution in original post

4 REPLIES 4
Kristjan76
Responsive Resident
Responsive Resident

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
Not applicable

Thanks! Worked perfectly. 

Thats great, how about thumbs up for that 😉

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.