Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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]))
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]))
Thanks! Worked perfectly.
Thats great, how about thumbs up for that 😉
this link may be of some help
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |