I have some MDX code to produce a 3 Months Average.
It doesn't work and I can't find the problem.
I've researched many sites - I'm trying to emulate the code found for similar scenarios but can't see what I'm doing wrong.
Calculated Member in SSAS MDX not working
I have a Fiscal Time dimension and a Fiscal Time Hierarchy in it
I expect to create a new measure which shows the average of the current month + the 2 past Months (3 Months Average), but for now, it only shows me NULL values
Result not showing the average, showing NULLs
Does anyone spot my mistake?
Thanks!
You should try with this formula (I tried to respect your dimension and measure name, tell me if I'm wrong):
AVG({[FISCAL TIME].[FISCAL TIME HIERARCHY].CURRENTMEMBER.LAG(2):[FISCAL TIME].[FISCAL TIME HIERARCHY].CURRENTMEMBER},[Measures].[AMOUNT or UNIT - WARRANTY])
Apparently, you can't specify a level (like FISCAL MONTH (Label Only)) when you're using hierarchy.
Be carefull, this formula works when you're browsing at "month" level, but if you go to day level, it will calculate 3 moving days (same principe for year and all other levels).
If you want to fix your calculation at 3 moving months,no matter the granularity, (month or lower) I would suggest you to use PARALLELPERIOD function :
AVG({PARALLELPERIOD([FISCAL TIME].[FISCAL TIME HIERARCHY].[FISCAL MONTH (Label Only)],2,[FISCAL TIME].[FISCAL TIME HIERARCHY].CURRENTMEMBER):[FISCAL TIME].[FISCAL TIME HIERARCHY].CURRENTMEMBER},[Measures].[AMOUNT or UNIT - WARRANTY])
I would also suggest you to add a scope to set the value of this calculation at NULL when you're at a higher level than Month.
I really hope it will solve your issue.
