Temp: Load * Inline[ ReportingMonth, MonthYear, ThisMonth 0, Oktober 2016, 447868 1, September 2016, 424493.99 2, Augustus 2016, 392533 3, Juli 2016, 286835 4, Juni 2016, 396899 5, Mei 2016, 355093 6, April 2016, 327492 7, Maart 2016, 326811 8, Februari 2016, 445798 9, Januari 2016, 400535 10, December 2015, 238079 11, November 2015, 268218.67 12, Oktober 2015, 330089.61 ];
Hi Jeroen, as Ioannis said, you can do it in chart using the below funtion:
The option given by Antonio is also a good one, sometimes is better have the previous values stored in the same row.
As a general comment, you tried yo dinamically use set analysis to filter ReportingMonth based on row values, this can't work because set analysis is applied before the table is calculated, so vTest will return null because it has many different values (row value not affecting). And in case it only has one value, this value will be applied to all rows (the same value)
The replies we received so far made us realize that we probalbly oversimplified this a little.
First off in the PrevMonth we actually need to sum the 'ThisMonth' values from the previous 12 months.So for October 2016 we need to calculate the sum of the values for the months September 2016 up until October 2015. This outcome is then used in a subsequent calculation step where is used as a denominator for dividing the Present value (which is the numerator) (but no need to get into those details).
Second, the user may slice on different dimensions like (product family, customer region, customer, etc...). The resulting measure which we are calculating should honor the filter selections set by the user of course. While it may be possible to calculate and store the sum of the previous 12 months at the atomic grain level it does not feel like an attractive approach.