Rolling Balances with Beginning Trial Balance

[bkjohn2016]

Hi,

I work as an accountant and am attempting to use financial data to create rolling balances. I have two Fact tables connected with various Dim tables. The first Fact table contains trial balance data at a given point in time i.e. end of month. The second table contains transaction data produced on a daily basis. I would like to add the transaction data to the previous year balance to produce a rolling balance.

I've created a measure to summarize the year to date transaction data:

TransactionSummary:=CALCULATE(SUM([NetBalanceChange]))

And a measure to calculate the previous years balance:

Beginning Balance:=CLOSINGBALANCEYEAR(SUM(FactTrialBalances[CURRENT BALANCE]),DimDate[Date])

Logic tells me that if I add the two I should get the results I'm looking for but it doesn't seem to work:


Rolling Balances:=[Beginning Balance]+[TransactionSummary]

I'm a bit new to the powerpivot environment and would appreciate any help you can offer. I'm sure it's just a filtering issue I've missed.

Thanks

 

[akice]

CLOSINGBALANCEYEAR returns "A scalar value that represents the expression evaluated at the last date of the year in the current context". So unless you are doing something different you have not specified, the function will return the value 'SUM(FactTrialBalances[CURRENT BALANCE])' for the last day of the current year (sum all summary data for only 12/31/2016 if the max year in filter context is 2016.) Not what you want I think. But if you make a slight change to the formula it should return 12/31/2015 ending balance.

PY Close Balance:= CLOSINGBALANCEYEAR( [COLOR=#574123]SUM(FactTrialBalances[CURRENT BALANCE]), PREVIOUSYEAR( DimDate[Date]) ) [/COLOR]

And if you want to be able to show a daily running balance of transactional data do

YTD Running Total := TOTALYTD( [NetBalanceChange], DimDate[Date] )

And finally:

YTD Running Total + BB:= [PY Close Balance] + [YTD Running Total]

 

[bkjohn2016]

Thanks Akice that worked great. Do you have a suggestion on how to stop the calculations at a specific date i.e. August 31, 2016. Currently, it's calculating through year end.

 

[akice]

That would depend on how you have the model organized and how you have the pivot organized. I do the same thing you are. I have a GL summary table that includes BB for the year; and a GL detail transaction table. I add the BB of the summary to the same year transactional data to get a running total for various GL account recons. Mine also carries forward the last ending balances to future periods with no data - but since purely internal worksheets we just live with it as a minor annoyance. I can think of a couple ways that may not show future periods, but would have to sit down and try a few things. I'll post back if i get the time to figure it out.