Handling cumulative GL account balances in Power Pivot

Overview

Suppose you import into Power Pivot monthly general (or nominal) ledger balances from your accounting/ERP system. Balance sheet account balances will usually be the balance at the end of the month, which is what you want. But commonly balance on each P&L GL account will be the cumulative balance to the end of that month from the beginning of the fiscal year. For reporting purposes it will usually be more convenient to have available in your Data Model the monthly P&L account balances rather than the cumulative YTD balances.

Logical Design

You can calculate these monthly balances by adding some calculated columns to your Data Model and adding some DAX functions in the format below. You shouldn't need to understand the precise workings of these DAX functions. This approach uses the EARLIER() function; the objective is to grab the prior month's cumulative account balance and subtract it from the month you are looking at to get just the monthly number. With later versions of the Data Model you can use DAX variables to achieve what the EARLIER() function does here - a topic for another post.

Fiscal Month Key

In your date table (I call it Time) you will need to add a column for FiscalMonthKey. This is:

[Fiscal Year Number] x 100 + [Number of the month in the fiscal year]

So if you have a fiscal year that ends on June 30, you might refer to the year from July 1, 2013 to June 30, 2014 as fiscal year 2014. Some might call it fiscal year 2013. It doesn't matter as long as you are consistent. Then August, for example, would be the second month in that fiscal year. So using the formula explained above the Fiscal Month Key would be:

2014 x 100 + 2 i.e. 201402

Similar logic can be applied where an organization has 13 4-week periods in a fiscal year.

Time table

I am maintaining the Time table in this example using an Excel linked table. This table has a similarly-calculated Time Key based off of the calendar month since that is the way it will likely be related to the GL data table.

Chart of Accounts

I have a fairly simplistic chart of accounts (COA) table that for each account flags it as a Flow or Balance. Flows are accounts that can be added over time; these are typically P&L account items such that a year's worth of data is the sum of the months. Conversely, for balance sheet accounts (such as cash, inventory, payables), the balance for the year is the balance at the end of the last month of the year not the sum of the months. In database modeling speak, balance sheet accounts are referred to as semi-additive.

Chart of accounts extract

Monthly cumulative account balances

The third Excel linked table represents the balances exported from the accounting/ERP system for 4 months:

  • Jun 2014 - fiscal 201412
  • Jul 2014 - fiscal 201501
  • Aug 2014 - fiscal 201502
  • Sep 2014 - fiscal 201503
For P&L accounts, the balances are cumulative from the beginning of the fiscal year. The Sep 2014 balance is the cumulative sum of transactions from Jul to Sep 2014.

Monthly balances extract

Power Pivot Data Model

The data model diagram looks like this

Data Model diagram

Most of the action takes place in the MonthlyBalances table:

Monthly balances table

The essence of this exercise is to determine the prior period balance (which will be zero in the case of the first fiscal month of the year). Then, for P&L accounts, subtract this from the (cumulative) month in question to get the total transactions just for the month. As mentioned, this uses the EARLIER() function thus:

[Prior period YTD amt] =
CALCULATE(SUM('MonthlyBalances'[YTD amt]),
FILTER('MonthlyBalances','MonthlyBalances' [FiscalMonthKey]=EARLIER('MonthlyBalances'[FiscalMonthKey])-1),
FILTER('MonthlyBalances','MonthlyBalances'[Account]=EARLIER('MonthlyBalances'[Account])))

The thing on the right hand side is the sum of [YTD amt] where the FiscalMonthKey is one less that the one we are looking at (i.e. the prior month), … and we are looking at the same account. If your data has a department, cost center, or similar on each row, you would need an additional filter something like:

FILTER('MonthlyBalances','MonthlyBalances'[Department]=EARLIER('MonthlyBalances'[Department]))

For want of a better explanation, the EARLIER() function allows you to find the same record (same account, same department) but for the prior month - hence the "- 1" in the formula above. Once you have that number you subtract it from the current month, in the case of a Flow account, or leave it as is in the case of a Balance account:

=IF(RELATED('ChartOfAccounts'[Flow or Balance])="FLOW",[YTD amt]-[Prior Period YTD Amt],[YTD amt])

In a later post I'll explain how you can use similar logic to do Current-Month-to-Same-Month-in-Prior-Year comparisons, by selecting just the current month in your pivot or cube formula report.

PivotTable extract

This report shows a selection of balance sheets accounts ("Balance") and P&L account accounts ("Flow") with the original cumulative balance and the calculated monthly balance for P&L accounts.

PivotTablle of cumulative and monthly balances

For the balance sheet accounts the cumulative YTD number and the monthly number are the same. For the P&L accounts the monthly numbers are the difference between the cumulative and the prior month cumulative. July is the first month of the fiscal year in this example so cumlative and monthly numbers are the same. The prior month returns zero because the DAX fomula is looking for FiscalMonthKey 201400 which has no data.

Final Thoughts

Like many things Excel/Power Pivot/life-in-general there are multiple ways to slice this cat, some probably better than the one I am suggesting above. My intention has been to minimize the number/complexity of DAX functions. Sometimes this is at the expense of adding extra calculated columns which consume resources. However finance and accounting applications modeled in Power Pivot are not commonly resource-intensive so little harm done, I think.

© 2011-17 Excelcraft.com

Home            Terms of Use            Privacy            Cookies            Contact