Workaround for passing TABLE reference to Excel LAMBDA function

You can use the new LAMBDA function in Excel (Desktop edition) to create custom, reusable functions and call them by a friendly name. The custom function is available throughout the workbook and can be called like native Excel functions.

The Excel Labs (formerly Advanced Formula Environment) add-in’s Import from Grid feature can be used to create the LAMBDA function from an example you create.

For the purpose of this article, I wanted to create a LAMBDA function to calculate the financial ratio (Receivables Turnover ratio for this example) from the Trial Balance table exported from Quickbooks Online. However, any Trial Balance table can be used as long as it follows the format used for this table. The following screenshot shows the part of the table used in this example.

The function takes the Financial Year as a parameter and calculates the Receivable Turnover ratio as follows:

Receivable Turnover = Revenue / Average of Beginning and Ending Accounts Receivable

I started by building my example (B2:E2) using the SUMIFS function as shown in the screenshot below.

I then used the Excel Labs add-in’s Import from Grid feature to build the LAMBDA function.

However, the add-in produced the following error because the Table reference cannot be used in the LAMBDA function.

A workaround for this limitation is to use the INDIRECT function to reference the required tables and columns in the LAMBDA formula. I changed the formulas as shown in the screenshot below.

=SUMIFS(

INDIRECT("TrialBalance[Balance]"),

INDIRECT("TrialBalance[Financial Year]"),A2,

INDIRECT("TrialBalance[Account Type]"),"Accounts Receivable (A/R)"

)

Clicking the Preview button generated the following code with a warning:

The warning is related to the INDIRECT function. The INDIRECT function is volatile because the range of the table is unknown and can change. As such it may cause the entire workbook to be recalculated frequently. Volatile functions can impact performance, especially for large workbooks. You can set the "Manual" calculation option to avoid automatic recalculation.

Here is the complete LAMBDA function generated by the add-in:

RecvTO = LAMBDA(FinYear,

    LET(

        EndAR, SUMIFS(

            INDIRECT(

                "TrialBalance[Balance]"

            ),

            INDIRECT(

                "TrialBalance[Financial Year]"

            ), FinYear,

            INDIRECT(

                "TrialBalance[Account Type]"

            ), "Accounts Receivable (A/R)"

        ),

        BegAR, SUMIFS(

            INDIRECT(

                "TrialBalance[Balance]"

            ),

            INDIRECT(

                "TrialBalance[Financial Year]"

            ), FinYear - 1,

            INDIRECT(

                "TrialBalance[Account Type]"

            ), "Accounts Receivable (A/R)"

        ),

        Revenue, SUMIFS(

            INDIRECT(

                "TrialBalance[Balance]"

            ),

            INDIRECT(

                "TrialBalance[Financial Year]"

            ), FinYear,

            INDIRECT(

                "TrialBalance[Account Type]"

            ), "Income"

        ) * -1,

        Revenue /

            ((EndAR + BegAR) / 2)

    )

);

You can verify your custom function by entering it in any cell. See the F2 cell below containing the custom function: =RecvTO(A2).

You can download the above Excel workbook here. Note that you will need to install the Excel Labs add-in to use the Import from Grid feature.

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.