SSAS MDX Calculated Member: 3 months moving average not working

0
votes

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

Fiscal Time Dimension

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!

1
sql-serverssasmdxmdx-queryanalysisservices
Hi, what do you mean by "It doesn't work" ? Do you have an incorrect result or no result at all ? – Hugo Salaun
Hi Hugo, I would expect the calculated member to show the average of the month and the 2 previous months, but right now is only throwing me nulls. I'm adding the corresponding image to my question. – user1288609

1 Answers

0
votes

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.