I'm looking to solve a WIP count by month question.
My data is updating daily and writing over previous repairs each day. When the unit is finally shipped, it will get a date in the "Ship_Date" column like below.
I would like to show for each month from the received date that the unit is not shipped out to count it as a WIP unit.
Something like below. I'm not sure if this is the best way to go about but would like to see if
Solved! Go to Solution.
To Round a date up to the last day of the month, one route is use:
1. DateTimeTrim([Date],"month") to round down to the first of the month
2. DateTimeAdd([Date],1,"month") to add one month, making it the first of the next month
3. DateTimeAdd([Date],-1,"day") to subtract one day, resulting in the desired last day of the month
This applied in the attached workflow uses this approach:
- Generate Rows to add record for each relevant end of month date, "WIP Inventory"
- Formula to add "In WIP"
Awesome! Exactly what I was looking to do. I know the generate rows was needed but wasn't sure how to incorporate. Thanks!
One question I just ran into with this and the generating row is how would you generate the row without ship dates?
What would the input data look like, and what is your expected output? If could be as simple as using a Filter tool prior to the generate records to pull those record without a date out, and then a Union too after to combine the two streams.
With the Generate Row you provided, anything that has NO ship date gets removed. Is there an addition to the generate rows formula I can add to include the no ship dates as YES in WIP?
One option is to wrap the reference to [SHIP_DATE] in an IfNull() that will use Today's Date if it is Null. This edit is made in the attached.
Edit, sorry IfNull() is not a built in function, post with fixed workbook in next reply
I'm receiving an IfNull Error on the generate rows.