12 Month Avg & Total Calc both Income Statement & Balance Sheet Accounts
Calc for a 12 Month Rolling Avg using MDX Function in ASO Cube
Case when (IsLeaf([Month].currentmember) and not (IsEmpty([Month].currentmember))) then
AVG (
{TUPLERANGE(
([Fyear].currentmember.lag(1), [Month].currentmember.NextMember), ([FYear].currentmember.lag(1), [Dec])),
TUPLERANGE(
([FYear].currentmember, [Jan]), ([FYear].currentmember, [Month].currentmember))})
Else
Missing
END
However this DOES NOT Work if the 12th month has a zero balance. The calc brings back a zero
Same happens for the 12 Month Rolling Total (Want the total for the past 12 months) here is that MDX Function from the ASO Cube
Case when (IsLeaf([Month].currentmember) and not (IsEmpty([Month].currentmember))) then