## Sum Prior Months into Current Month (Webi)

January 6, 2020 2 Comments

Yes, this was an actual request, and when it came in, I honestly wasn’t sure if it was possible. Open orders are allocated to the month in which they are scheduled to ship. But some orders, scheduled to ship in past months, for whatever reason, didn’t ship when scheduled. They are still open. So the client wanted the open order amounts for prior months to be summed into the current month, while the amounts for future months will stay as scheduled. Well, I hate to say no to any request, so I went to work on it, and, with the use of a flag variable, and calculation contexts, I was able to make it work.

Let’s imagine that the raw results from our query looks like the following, and the current month is January, 2020.

As you can see, there is quite a bit of data for the prior months. But I need all open_amounts from previous months to be brought forward into the current month. To begin with, I needed flag variable that would flag the months, as needed. Flag variables are very handy for many purposes, and most of the time, will return one of two values. However, in this case, I need a three way flag variable:

- If the month is past, flag it with a 1.
- If the month is the current month, flag it with a 0.
- If the month is a future month, no flag is needed.

so, to start with, I created a dimension variable called Completed Months Flag. Here is the formula:

=If([invoice_month] < MonthNumberOfYear(CurrentDate()) And [invoice_year] <= Year(CurrentDate());1;

If([invoice_year]<Year(CurrentDate());1;

If([invoice_month] = MonthNumberOfYear(CurrentDate()) And [invoice_year] <= Year(CurrentDate());0)))

Let’s break this down. The first line will return a 1 for all previous months in the current year. So, this being January, this part will not be returning any results for any one row.

The second line will return a 1 for all previous months in prior years. In this case, it will return a 1 for the months of July – December, 2019.

The third line will return a 0 only for the current month, January, 2020.

When I add the flag variable to the table, I see the following results.

So now, I know which months need to be summed up (those months marked by a 1), and where they need to be summed up (the month with a 0). So now, I need to take advantage of calculation contexts, and create the measure variable, called Open Forward, that will sum the prior months into the current month. Here is the formula.

=Sum(If([Completed Months Flag]<>1;Sum([open_amount] In Block Where([Completed Months Flag]=1)))) Where([Completed Months Flag] = 0) + Sum([open_amount]) Where([Completed Months Flag] <> 1)

If you think this looks complicated, you’re right. So let’s walk slowly through this formula, part by part.

We start with an If function. If the flag is not equal to 1, that is, the current and future months, then sum the open_amount for the entire block, but only where the flag does equal 1, that is, all previous months. But only do this where the flag is equal to 0, that is, the current month. Let me restate that. Wherever there is a 1 for the flag variable, sum up all values in the block, and place that sum on the row where the flag is equal to 0. So all previous months values are summed into the current month. If we stopped there, we would get the prior months summed to the current month, but all future months would be left blank, and the open_amount for the current month would also be blank. So that leads us to the final part. This one simply adds the sum of the open_amount where the flag does not equal 1. So each month, from the current month going forward, simply displays the open_mount for that month. In the case of the current month, that amount is added to the sum of the previous months. With a little formatting, we end up with a table that looks like this:

Of course, you can remove the flag variable, and the open amount columns from the table, and you have the final results. Needless to say, I was pretty excited to actually make this work, and the client was quite pleasantly surprised. I also made a version of this in Tableau, and will have a post on that soon.