Next 12 months calculation excluding months when value is zero

Question

I have a set of data where I have calculated the Next 12 months, but there are some months where the values are zero (see under Order Date the months of Feb 2018 and March 2018) and they were filtered out. I need to sum the next 12 months values, but before doing so, I want to include a formula that updates the Next 12 month values if the count of the values is not 12. In this case, the count is only 10, so I need to pick up the amounts for January 2019 and February 2019 and show those under the Next 12 Months column.   See screenshot below.

Liz001 · Accepted Answer

Attached I have included my current workflow, which calculates the next 12 months column, and also a workflow to show what I want the total to be.

To calculate the next 12 month, I used the minimum order date and calculated from the next 12 months end date. Since there are some months in between those dates where the values are zero, the end 12 month period date includes those zero values. So, instead of fixing the end date, I thought maybe at the end of the workflow, just before the summarize tool, we can put a tool to update the next 12 month column to pick up an additional two months of data.

Thank you!

fmvizcaino · Accepted Answer

Hi @Liz001 ,

Attached is a workflow with a fix for your next 12 months calculation.

Let me know if that works for you.

Best,

Fernando V.