OK, that's clear now. Do you have a dimension of year-month? And what are you counting?

Hi Immanuel Gem

OK.

First off, if this is a model you have built, then it would be much, much easier if you created a dimension of year-month or alternatively easier if you have a year --> month hierarchy. This is because then your months will automatically wrap around your years.

Second, if this is not your model and it is based on a SQL DB, do you have a Date field in your tables? If so, then you can create a year-month dimension dynamically using custom columns and progress as above.

Thirdly if the answer to all the above is no, then we have to determine what the combination of month and year for each of the prior three months should be from your selection. This is best done using parameters to select the month and year and using these values to define the combination of month and year to use in the calculation.

First create parameters for Year and Month:

We now need to think about the rules for defining the selection of Month and Year. We need the prior 3 months to the selected Month and Year. So if the User selects anything other than January, February or March, then all the months will be coming from the selected year and we don't have to worry about it. If the user selects January, then the prior 3 months are all in the previous year. If February, then November and December, if March then just December. So we need to test for the month selected and assign the year accordingly to what is selected. 

A we have 4 options to choose from, I'm going to use the CASE statement rather than nesting loads of IFs. Pyramid constructs its CASE statement by stating the Criteria in  a list, then the matching results in  a second list. We can test which month was selected in the parameter by testing its caption. The first criteria tests that the selected month is not in Jan, Feb or Mar, i.e. it is one of the months where we don't have to think about the year.

We then test to see which month from Jan, Feb or Mar was selected:

We now list the results we want to match the Criteria. If it is not Jan, Feb or Mar, then simply add the previous 3 months using the selected year together and using the Lag function on the month parameter. Note the little "fx" sign on the row when the parameter is selected. This allows you to specify the month parameter with a lag, lead, previous, next function. So the three months are the selected year parameter for all, with the month parameter with lag 1, 2 and 3 assigned respectively.

 

We now need to deal with Jan, Feb and Mar.

For January, we need to select Oct, Nov and Dec from the year previous to the one selected. We can specify each of the months explicitly, the year being defined as a lag(1) function on the Year parameter.

For February, we need Nov and Dec from past year and January from this year. Again use the lag function on the data points for November and December, but not for January.

I've also used the dynamic caption here to make it easier to see what has been selected by using the parameter captions in the caption of the calculation.

Here we can see the results. Note that the calculation is saved into the Month dimension, so it can apply to any measure.

Hope that helps!

Ian