[Solved] Moving Totals using DMR Model

[guy222kool]

Hello Cognos Gurus,

I have a requirement to calculate a 3 Month Moving Total of a Measure, i.e. Total Current Month and 2 preceding Months.
The Moving Total would be calculated against the Month Level in the report.
And the Calculation should 'reset' at the end of each Year.

In relational terms my calculation would look like this:

Code Select Expand

moving-total ([Revenue],3 for [Year])

But I need to achieve the same functionality with a  Dimensional Package.
I have tried the following Code and it worked perfectly, except, the Moving- Total calculation is not getting 'reset' at the end of each year.

Code Select Expand

total([Revenue] within set lastPeriods (3,currentMember ([Sales].[Time dimension].[Time dimension])))

I'm aware that the 'periodsToDate ' function can be used to reset the calculation at the start of a new year by using the Year Level.
But I dont know if this could be used along with the 'lastPeriods' function to achieve my requirement.

Please advise on this.

[guy222kool]

Its Done!!! :)
I've achieved this requirement using a combination of Dimensional Functions and the report works exactly like I needed.

Code Select Expand

total([Revenue] within set
intersect (periodsToDate ([Sales].[Time dimension].[Time dimension].[Year],currentMember ([Sales].[Time dimension].[Time dimension])),
set (currentMember ([Sales].[Time dimension].[Time dimension]),
lag (currentMember ([Sales].[Time dimension].[Time dimension]),1),
lag (currentMember ([Sales].[Time dimension].[Time dimension]),2))
   )

)
Now, before the Logic begins to confuse, I created 2 different sets One using the 'periodsToDate' function which resets the calculation for each year.
1st Set: Contains members from the First Member of the Year to the Current Member(depending on the row)
2nd Set: Contains 3 Members: Current Member, Current Member -1 and Current Member -2

The intersection of these 2 sets gives me the Current Member and previous 2 members, resetting the Calculation for each year.
I'm also attaching an image which can offer a better explanation.

Although I'm not sure if this is the best way but it certainly worked for me  ;D
Let me know any thoughts on this.