I am trying to design a model where I want to sum the input values, get the total for the current month and carry forward it to next month as an input.
I need the calculations to start from the current month till end of year will have columns for all 12 Months.
Name | Value | JAN | FEB | MAR | APR |
A | 10 | 1 | 1 | 1 | 1 |
B | 20 | 2 | 2 | 2 | 2 |
C | 30 | 3 | 3 | 3 | 3 |
D | 40 | 4 | 4 | 4 | 5 |
Expected Output:
A Month Next Month .... till Last Month of current Year.
Value Previous Month Sum
Month Column Value Month Column Value
Sum Sum
Solved! Go to Solution.
Hi @Whenky
Are you looking for a YTD type formula, calculated on the month level?
So for January, 1+2+3+4=10
Febraury: 10+1+2+3+4=20 (carry over the 10 from January, and add it to the values for February)
Cheers,
Esther
(A mockup of your output with some values in there could help as well)
@estherb47 No I'm Expecting Some thing Like for current month:
A AUG SEP OCT NOV DEC
10 11 13 16 20
1 2 3 4 5
11 13 16 20 25
Hi @Whenky
How about this approach.
To calculate based on the prior month, transpose your data to vertical, and then filter out on the dates that include the current month and beyond. This is done with a few date functions, to pull a text string representing the month, from the text month header.
The Value column from the transpose tool becomes the second row in the result, so renamed it with a select tool as Row2.
2 Multi-Row formula tools create the totals, and pull in values from the previous month, if later than the current month.
Another transpose tool breaks out those new columns into rows, so that we can build the desired table.
Rearrange to the correct order with a Sort, and then rebuild your table with Crosstab.
Please let me know if that helps.
Cheers!
Esther
Thanks @estherb47 It helped.