# Running Total

• ### Question

• Hi Guys,

Im trying to create a balance sheet using my AS cube. I need to create a running total month on month which i can do by using this:

Sum({
{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
[Date].[Calendar].CurrentMember
}, [Measures].[Amount])

However, when a new year starts i need the running total to start from 0 again, is there any easy way to do this?

Example

2011                                          2012

1    2     3    4    5    6    etc       1     2     3

Account Group   5    10  25   30  55   66               6    10   88

Cheers

Dave

Wednesday, June 5, 2013 8:19 PM

• Think i may have solved it:

SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Amount])

However, i'd be happy to hear any other way I could do this.....

Cheers

Dave

• Marked as answer by Thursday, June 20, 2013 6:34 AM
Wednesday, June 5, 2013 8:28 PM

### All replies

• Think i may have solved it:

SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Amount])

However, i'd be happy to hear any other way I could do this.....

Cheers

Dave

• Marked as answer by Thursday, June 20, 2013 6:34 AM
Wednesday, June 5, 2013 8:28 PM
• That's exactly what you want.

It used to be that it was faster to write your own MDX to add up the days in the current period, then add up the earlier periods.  This uses fewer cells than a query that adds up all the days from start of year.  However, this does not appear to be the case any more, so your query is idea.

Richard

Richard

Thursday, June 6, 2013 12:09 AM
• Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.

SELECT      T1.SL,
T1.GroupName,
T1.Amount,
SUM(T2.Amount) as CumulativeSum
FROM  @Temp T1 INNER JOIN
@Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount
ORDER BY T1.SL

for more with example you can visit: http://cybarlab.blogspot.com/2013/06/cumulative-sum-in-sql.html

Thursday, June 6, 2013 5:51 AM
• Thanks Richard,

Perhaps you can help me with this. I need to create a calculated member which calculates a YTD value at one level (and children) in my hierarchy and then a running total at another level (and children).

An example of the mdx which i cant get working:

Iff([Account].[Accounts].&[1].Children = 'Balance Sheet', SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Amount]),
Sum({
{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
[Date].[Calendar].CurrentMember
}, [Measures].[Amount]))

Thursday, June 6, 2013 10:36 AM
• Im looking to achieve:

case
when
[Account].[Accounts].currentmember.membervalue.children = 'Balance Sheet' then

Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
[Date].[Calendar].CurrentMember
}, [Measures].[Amount])
When
[Account].[Accounts].currentmember.membervalue.children = 'Net Income' then

[Measures].[Amount]

End

Any help would be hugely appreciated.

Regards

Dave

• Edited by Thursday, June 6, 2013 12:36 PM
Thursday, June 6, 2013 11:09 AM
• Im almost there, can anyone assist with the last little bit. I need the calculation to be applied to all the decedents of the required level.

I currently have.

IIF
( [Account].[Accounts].CurrentMember.membervalue = "Assets"

,Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
[Date].[Calendar].CurrentMember
}, [Measures].[Amount])
, [Measures].[Amount]
)

Thursday, June 6, 2013 12:50 PM
• with member measures.[my calc] as
case
when
[Account].[Accounts].currentmember.membervalue.children = 'Balance Sheet' then
Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
[Date].[Calendar].CurrentMember
}, [Measures].[Amount])
When
[Account].[Accounts].currentmember.membervalue.children = 'Net Income' then
Sum([Account].[Accounts].currentmember,[Measures].[Amount])
else null
end
select measures.[my calc] on 0,
nonempty( [Account].[Accounts].allmembers * [Date].[Calendar].allmembers, [Measures].[Amount]) on 1
from [CubeName]

Let me know if this works. I am not sure about the logic you placed in the first 'when .. then ' statement

Regards, Anant

Thursday, June 6, 2013 7:52 PM
• Thanks Anant but this doesn't work.

Im almost there with the following query but  can anyone assist with the last little bit. I need the calculation to be applied to all the decedents of the required level.

I currently have.

IIF
( [Account].[Accounts].CurrentMember.membervalue = "Assets"

,Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
[Date].[Calendar].CurrentMember
}, [Measures].[Amount])
, [Measures].[Amount]
)

Thursday, June 6, 2013 8:14 PM