WIP Count by Month

[ashleyinman]

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 

[Joe_Mako · Accepted 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"

[ashleyinman]

Awesome! Exactly what I was looking to do. I know the generate rows was needed but wasn't sure how to incorporate. Thanks! 

[ashleyinman]

One question I just ran into with this and the generating row is how would you generate the row without ship dates? 

[Joe_Mako]

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.

[ashleyinman]

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?

[Joe_Mako]

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

[ashleyinman]

I'm receiving an IfNull Error on the generate rows.

[Joe_Mako]

here is the expression I added:

IF IsNull([SHIP_DATE]) THEN DateTimeToday() ELSE [SHIP_DATE] ENDIF