I have used the following script to return each weekly average for a measure:

However, I am struggling trying to return only the last 4 weekly averages.

I have tried using the range operator and LAG(4) : NULL but I have had no luck. I need to create a named set or calculated member which defines the last 4 week range and then use in my script.

• Edited by DarrenOD Friday, January 30, 2015 3:12 PM

I was able to get it work using the LastPeriods function:

However, I hard coded the date for testing. How do I get the current week member for dynamic script?

• Edited by DarrenOD Friday, January 30, 2015 3:22 PM

you'll need to build it using StrToMember and some VB functions.
https://msdn.microsoft.com/en-us/library/82yfs2zh(v=vs.90).aspx

Below is an example that should get you going on the right path...

you'll need to change the format string to match your date key (my cube uses YYYYMMDD) and adjust the arithmetic in the [FirstOfWeek] based on the day of the week you use for the first day of the week...