Result from month based on previous months

[#1]

Hi,

We are struggling with set analysis trying to calculate a result for a specific month based on values from previous months. For sake of simplicity we boiled this down to its essentials.

Consider the following table:

ReportingMonth	MonthYear	This Month	Prev Month
0	Oktober 2016	447868	0
1	September 2016	424493,99	0
2	Augustus 2016	392533	0
3	Juli 2016	286835	0
4	Juni 2016	396899	0
5	Mei 2016	355093	0
6	April 2016	327492	0
7	Maart 2016	326811	0
8	Februari 2016	445798	0
9	Januari 2016	400535	0
10	December 2015	238079	0
11	November 2015	268218,67	0
12	Oktober 2015	330089,61	0

In the 'Prev Month' column (where currently only zero's are displayed) we want the value from the next row to appear. We tried doing so using set analysis as follows:

Sum({$<ReportingMonth={"$(=Test2)+1"}>}InvoiceQuantity)

Test2 is a LET variable: LET Test2 = 'ReportingMonth + 1';

The above table should look as follows:

ReportingMonth	MonthYear	This Month	Prev Month
0	Oktober 2016	447868	424493,99
1	September 2016	424493,99	392533
2	Augustus 2016	392533	286835
3	Juli 2016	286835	396899
4	Juni 2016	396899	355093
5	Mei 2016	355093	327492
6	April 2016	327492	326811
7	Maart 2016	326811	445798
8	Februari 2016	445798	400535
9	Januari 2016	400535	238079
10	December 2015	238079	268218,67
11	November 2015	268218,67	330089,61
12	Oktober 2015	330089,61	398637

We have gone through a whole bunch of variations as well as through a miriad of posts on the community. No luck and pretty frustrating. So any help will be highly appreciated.

Thanks!

[rubenmarin]

Hi Jeroen, maybe this blog post helps you to make easier accumulations:The As-Of Table