Build PowerPivot Data Model for Financial Analysis in Dynamics GP

In the previous article, I described the steps to build a PowerPivot data model to analyze Microsoft Dynamics GP Sales data. In this article, I will explain the steps I used to build a data model that can be used for financial analysis using General Ledger data in Dynamics GP. (See the related article Upgrade PowerPivot Data Model to SSAS Tabular).

The following screenshot shows the final PowerPivot data model in the diagram view.

The first steps in building this data model is to retrieve opening and closing balances and net change for each account and period in General Ledger. The query to retrieve this data can be found in the article SQL to Create GL Account Balances Table for Dynamics GP. The other tables include Accounts, Calendar and Period. The Calendar and Period tables are explained in the previous article. The Accounts table uses the Accounts view in Dynamics GP database.

Once we have the above tables created, we can create the calculated fields required for financial analysis. First, I created the base fields used in calculating various financial ratios. The following table shows the base fields, DAX formulas and some additional information.

Note that the DAX formulas are specific to the sample company (TWO) Chart of Accounts and Account Categories. You may have to modify these formulas to work with your company’s Chart of Accounts and Account Categories.

Calculated Fields

Calculated Field	DAX Formula	Additional information
ClosingBalance	CALCULATE(SUM([Ending Balance]), LASTDATE(Calendar[Date]))	Closing Balance for the last date based on selected dates
Activity	SUM([Net Change])	Total Net Change based on selected dates
ActivityYTD	[Activity](DATESYTD(Calendar[Date]))	Total YTD Net Change based on selected dates
ActivityQTD	[Activity](DATESQTD(Calendar[Date]))	Total QTD Net Change based on selected dates
ActivityPrevYearQTD	[Activity](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR))	Total QTD Net Change last year based on selected dates
ActivityPrevYearYTD	[Activity](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR))	Total YTD Net Change last year based on selected dates
ActivityPrevYear	[Activity](DATEADD(Calendar[Date],-1,YEAR))	Total Net Change last year based on selected dates
ActivityMTD	[Activity](DATESMTD(Calendar[Date]))	Total MTD Net Change based on selected dates
ActivityPrevYearMTD	[Activity](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR))	Total MTD Net Change last year based on selected dates
PeriodActivity	IF(HASONEVALUE(Period[Period]),  SWITCH(VALUES(Period[Period]),  "Current", [Activity],  "MTD", [Activity](DATESMTD(Calendar[Date])),  "QTD", [Activity](DATESQTD(Calendar[Date])),  "YTD", [Activity](DATESYTD(Calendar[Date])),  "PrevYear", [Activity](DATEADD(Calendar[Date],-1,YEAR)),  "PrevYearMTD", [Activity](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR)),  "PrevYearQTD", [Activity](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR)),  "PrevYearYTD", [Activity](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR)),  BLANK()))	Total Net Change for selected dates and period (Current, MTD, QTD, etc.). This field can be dragged to the Pivot Table or report along with dates and period to dynamically calculate the Net Change amount.
CurrentAssets	CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable" ||     RELATED('Accounts'[Account Category]) = "Cash"||     RELATED('Accounts'[Account Category]) = "Inventory"||     RELATED('Accounts'[Account Category]) = "Prepaid Expenses"||     RELATED('Accounts'[Account Category]) = "Work in Process"||     RELATED('Accounts'[Account Category]) = "Notes Receivable"||     RELATED('Accounts'[Account Category]) = "Short-Term Investments"))	Total Current Assets for the last date based on selected dates
CurrentLiabilities	CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Accounts Payable" ||     RELATED('Accounts'[Account Category]) = "Dividends Payable"||     RELATED('Accounts'[Account Category]) = "Interest Payable"||     RELATED('Accounts'[Account Category]) = "Notes Payable"||     RELATED('Accounts'[Account Category]) = "Other Current Liabilities"||     RELATED('Accounts'[Account Category]) = "Taxes Payable"))*-1	Total Current Liabilities for the last date based on selected dates
CurrentAssetsBeg	CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable" ||     RELATED('Accounts'[Account Category]) = "Cash"||     RELATED('Accounts'[Account Category]) = "Inventory"||     RELATED('Accounts'[Account Category]) = "Prepaid Expenses"||     RELATED('Accounts'[Account Category]) = "Work in Process"||     RELATED('Accounts'[Account Category]) = "Notes Receivable"||     RELATED('Accounts'[Account Category]) = "Short-Term Investments"))	Total Current Assets for the first date based on selected dates
AvgCurrentAssets	([CurrentAssetsBeg]+[CurrentAssets])/2	Average of beginning and ending Current Assets based on selected dates
Inventory	CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Inventory"))	Ending Inventory balance based on selected dates
InventoryBeg	CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Inventory"))	Beginning Inventory balance based on selected dates
AvgInventory	([InventoryBeg]+[Inventory])/2	Average of beginning and ending Inventory
AccountsReceivable	CALCULATE(SUM([Ending Balance]), LASTDATE(Calendar[Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable"))	Ending A/R
AccountsReceivableBeg	CALCULATE(SUM([Beginning Balance]), FIRSTDATE(Calendar[Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable"))	Beginning A/R
AvgReceivable	([AccountsReceivableBeg]+[AccountsReceivable])/2	Average of beginning and ending A/R
NetSales	CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Sales" ||     RELATED('Accounts'[Account Category]) = "Sales Returns and Discounts"))*-1	Net Sales based on selected dates
COGS	CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Cost of Goods Sold" ))	Cost of Goods Sold based on selected dates
TotalLiabilities	CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Accounts Payable" ||     RELATED('Accounts'[Account Category]) = "Dividends Payable"||     RELATED('Accounts'[Account Category]) = "Interest Payable"||     RELATED('Accounts'[Account Category]) = "Notes Payable"||     RELATED('Accounts'[Account Category]) = "Other Current Liabilities"||     RELATED('Accounts'[Account Category]) = "Taxes Payable"||     RELATED('Accounts'[Account Category]) = "Long-Term Debt"))*-1	Ending Total Liabilities
TotalEquity	CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Common Stock" ||     RELATED('Accounts'[Account Category]) = "Retained Earnings"))*-1	Ending Total Stockholders’ Equity
TotalLiabilitiesBeg	CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Accounts Payable" ||     RELATED('Accounts'[Account Category]) = "Dividends Payable"||     RELATED('Accounts'[Account Category]) = "Interest Payable"||     RELATED('Accounts'[Account Category]) = "Notes Payable"||     RELATED('Accounts'[Account Category]) = "Other Current Liabilities"||     RELATED('Accounts'[Account Category]) = "Taxes Payable"||     RELATED('Accounts'[Account Category]) = "Long-Term Debt"))*-1	Beginning Total Liabilities
TotalEquityBeg	CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Common Stock" ||     RELATED('Accounts'[Account Category]) = "Retained Earnings"))*-1	Beginning Total Stockholders’ Equity
NetIncome	CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED(Accounts[Financial Statement]) = "Profit and Loss" ))*-1	Net Income based on selected dates
InterestExpense	CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED(Accounts[Account Description]) = "Interest Expense" ))	Interest Expense (Note: I used the Account description for this purpose because the sample COA uses Other Expenses category for this account)
TaxExpense	CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Tax Expense"))	Tax Expense
GrossProfit	[NetSales]-[COGS]	Gross Profit based on selected dates
AvgTotalAssets	([TotalLiabilities]+[TotalLiabilitiesBeg]+[TotalEquity]+[TotalEquityBeg])/2	Average Total Assets for the selected dates
AvgTotalEquity	([TotalEquity]+[TotalEquityBeg])/2	Average Total Equity for the selected dates
NumberOfDays	1.0*(LASTDATE(Calendar[Date])-FIRSTDATE(Calendar[Date]))+1	Number of Days in the selected date range. This field is used in Days calculation in the ratios below.

Now that all the base fields are defined, we can create the calculated fields for financial ratios. The following tables show the financial ratios and the DAX formulas. They are divided into the following categories: Liquidity, Solvency and Profitability.

Liquidity Ratios

Calculated Field	DAX Formula	Additional information
CurrentRatio	[CurrentAssets]/[CurrentLiabilities]	Current Ratio
QuickRatio	([CurrentAssets]-[Inventory])/[CurrentLiabilities]	Quick Ratio or Acid-test Ratio
InventoryTurnover	[COGS]/[AvgInventory]	Inventory Turnover
AvgDaysInInventory	[NumberOfDays]/[InventoryTurnover]	Average Days in Inventory
ReceivableTurnover	[NetSales]/[AvgReceivable]	Receivable Turnover
AvgCollectionPeriodDays	[NumberOfDays]/[ReceivableTurnover]	Average Collection Period in Days

Solvency Ratios

Calculated Field	DAX Formula	Additional information
DebtToEquityRatio	[TotalLiabilities]/[TotalEquity]	Debt to Equity ratio
TimesInterestEarned	([NetIncome]+[InterestExpense]+[TaxExpense])/[InterestExpense]	Times Interest Earned Ratio

Profitability Ratios

Calculated Field	DAX Formula	Additional information
GrossProfitRatio	[GrossProfit]/[NetSales]	Gross Profit Ratio
ProfitMargin	[NetIncome]/[NetSales]	Profit Margin
ReturnOnAssets	[NetIncome]/[AvgTotalAssets]	Return on Assets
AssetTurnover	[NetSales]/[AvgTotalAssets]	Asset Turnover
ReturnOnEquity	[NetIncome]/[AvgTotalEquity]	Return on Equity

The following screenshot shows the results of these ratios from the sample company (TWO) data.

These ratios will be automatically recalculated when you refresh the PowerPivot data model to reflect updated GL data. You can enhance this data model by creating KPIs based on industry benchmarks and historical data for your company.