Overview

The Cashflow and Networth Analysis Excel Workbook was designed to help you better understand your personal finances by providing detailed insights into your spending, income, assets, and liabilities over time, and by making your progress towards financial independence visible using powerful data visualizations.

The workbook was designed to integrate seamlessly with Tiller as an extension to the Tiller foundation template but it also functions as a standalone workbook for anyone who wants to import their data through other means.

The workbook was built without using any macros, scripts or VBA code. This decision was made for two main reasons: To make the spreadsheets more accessible to those who aren’t familiar with programming and VBA, and to meet the requirements outlined by the Tiller Community Builder Rewards Program.

Map

To help guide you through the numerous spreadsheets included in the workbook and their relationships, the following map was created:

This map depicts the flow of data between spreadsheets (dependencies). At a high level, the data flows from the Foundation Sheets (Transactions, Categories, Balance History, Accounts, and Balances) to hidden Intermediate Sheets, and finally to dashboards that summarize the data and provide user controls to filter the data as required.

Intermediate Sheets such as Spending By Group Total transform the raw data present in the Foundation Sheets to prepare it for display in user friendly ways via the dashboards. For the most part, these sheets can remain hidden and ignored unless you want to dig deep into individual spending transactions or troubleshoot a dashboard issue.

The Settings sheet sits apart from this flow. This sheet allows you to configure global settings that shape what every dashboard displays.

Sheet Overview

Foundation Sheets

The Foundation Sheets are the core sheets that store your “fact” data (Transactions and Balance History) and “dimension” data (Categories, and Accounts). The Balances sheet functions as a dashboard but is also referenced by other dashboards.

Settings

The Settings sheet is the workbook’s control panel. Use it to configure the following global settings:

The Global Exclude Tag and Inflation Adjustment settings only take effect after a data refresh (see Updating Data), because they feed calculations performed when your data is loaded rather than when the sheet is drawn. The Present Month and Safe Withdrawal Rate settings apply immediately.

Excluding the Present Month

The workbook was designed predominantly for reviewing your finances on a monthly basis. The current month is only ever partially complete, so including it makes charts misleading and quietly distorts your averages.

To exclude the present month, select FALSE. If you want to see all months including the present month, select both TRUE and FALSE.

This setting matters more than it appears on the Spending By Group Average and Income By Category Average sheets. Those averages divide your totals by the number of months that actually contain data, and a partially complete month still counts as a whole one. Review your spending on the 3rd of January with the present month included and your monthly averages will be dragged down by a month that’s only a tenth over. Leaving the present month excluded is the safer default.

Spending Dashboard and Income Dashboard

The Spending Dashboard and Income Dashboard allow you to analyze your spending and income over time by category and group. The dashboards and their Intermediate Sheets are practically identical save for the separation of income and spending transactions, and the increased focus on categories rather than groups for income (since most people will have considerably fewer income categories than spending categories).

Use the slicers on the left to filter the data by year, month, group, category, and tag as desired (see Tagging Transactions for what the Tags slicer does). Use the plus/minus buttons on each pivot chart to switch between aggregating the data monthly and yearly.

If you want to dig into the specific transactions that make up the current selection, unhide the Intermediate Sheet of interest (for example Spending By Group Total) and double-click on the pivot table cell of interest. This will generate a table of the transactions that make up that cell value as shown below. When you’re finished analyzing the transactions, delete the generated sheet (Sheet 1) and hide the Intermediate Sheet.

The Intermediate Sheets contain pivot tables summarizing the data in the following ways:

• Total by Category
• Total by Group
• Average by Group (spending) or Category (income)

Networth Dashboard

The Networth Dashboard allows you to analyze your assets, liabilities, and resulting networth over time. Use the Date Range timeline to select the date range for the data.

The first Intermediate Sheet Balances by Month uses a combination of pivot tables and standard Excel formulas to summarize the balance history data by month (using the latest balance for each month). It also incorporates the account dimension data (account name, group, class) and calculates other useful information about each monthly balance.

Balances by Month is then referenced by four more Intermediate Sheets: Assets By Month, Liabilities By Month, Assets By Year, and Liabilities by Year. These sheets also use a combination of pivot tables and regular Excel formulas to separate the data into monthly and yearly assets and liabilities organized by group. As with monthly data, the yearly balance data reports the latest available balance for the year. The monthly sheets also perform networth related calculations.

Cashflow Dashboard

While the spending and income sheets allow you to analyze your income and spending predominantly by categories and groups, The Cashflow Dashboard allows you to analyze your total income and spending over time, your savings rate, and the frequency of your monthly spending via a histogram. Moving average trendlines for spending and income are also included to help identify longer term trends. Use the Date Range timeline to select the date range for the data.

The histogram implementation for this dashboard was created using a custom bar chart rather than the built-in Excel histogram. Use the histogram overflow bin minimum and maximum input cells to define the bounds of your spending histogram. Any monthly spend greater than the bounds specified will be incorporated into these overflow bins. For example, setting the maximum overflow bin to $5000 would mean that any months featuring spend greater than $5000 would all be counted in the column “> $5000”. The bin widths are set to $500 by default. You can see how the histogram is generated in the Income and Spending Monthly Intermediate Sheet.

The figures on this dashboard are inflation adjusted using the Inflation Adjustment setting on the Settings sheet. This adjustment is important when analyzing spending across multiple years, since nominal monthly spend values will give you a somewhat inaccurate picture of how your spending is changing over time. If this adjustment isn’t desired, set the inflation rate to 0%. Any changes to this rate require a data refresh to take effect (see Updating Data).

The Income and Spending Monthly and Income and Spending Yearly Intermediate Sheets contain a combination of pivot tables and regular Excel formulas to aggregate the spending and income by month and year, as well as perform the savings rate and histogram calculations.

Year to Date Summary Dashboard

The Year to Date Summary Dashboard allows you to analyze a summary of your year to date finances. It displays monthly cashflow in tabular and chart form, charts of spending and income by category, and a summary of current networth. Use the Year timeline in the top left to select the year of interest.

The Year to Date Summary Dashboard doesn’t depend on any Intermediate Sheets and draws its data directly from the Foundation Sheets.

Cashflow and Networth Summary Dashboard

The Cashflow and Networth Summary Dashboard attempts to summarize all of your key financial data and progress towards financial independence in a single chart. This chart features two vertical axes: The left axis displays monthly cashflow (income, spending, and safe withdrawal income), while the right axis displays networth. Moving average trendlines for spending and income are included to help identify longer term trends.

The sheet takes the Safe Withdrawal Rate you set on the Settings sheet, calculates a monthly safe withdrawal income from your current networth, and plots it on the chart. When your yellow safe withdrawal line crosses your red average spending line, congratulations! You’ve achieved Financial Independence.

Use the Cashflow Date Range and Networth Date Range timelines to control the period shown on each axis.

The Cashflow and Networth Summary dashboard draws its data from the Assets By Month, Liabilities By Month, and Income and Spending Monthly Intermediate Sheets.

Tiller Integration and Initial Setup

See Tiller’s Getting Started Guide and Foundations Guide for an overview on setting up Tiller to integrate your accounts with the workbook and set up the Foundation Sheets.

Once you have a Tiller account setup and linked to the workbook, you can import your transactions and account data. Follow the Tiller guides above to fill your workbook using the Tiller Money Feeds add-on.

For non-Tiller users, you won’t be able to link your accounts and fill your workbook using Tiller Money Feeds, but the rest of the information in the guides above for setting up your Foundation Sheets is still applicable.

To import existing transactions and balance history records, you can copy-paste them directly into the Transactions and Balance History Foundation Sheets. Ensure that the data matches the column structure and cell formatting as closely as possible before copying, as mismatches can create problems when refreshing the data.

The workbook initially contains placeholder data in the Foundation Sheets intended to be overwritten with your data. The purpose of this placeholder data is to help keep the workbook in a valid state, as removing all data can cause errors and changes to pivot table configurations. Overwrite and add to this sample data rather than deleting it first to reduce the likelihood of problems. Take extra care not to perform a data refresh when no data is present. If you do, you will likely be better off starting again from a fresh workbook rather than trying to address the issues.

Finally, it’s time to configure your Accounts and Categories. You can verify your accounts are setup correctly by inspecting the Balances sheet.

General Usage

Updating Data

Once the initial setup is complete you can update the workbook periodically with new Transaction and Balance History entries as they come in.

Next, you must categorize your transactions. Follow the guidelines described in the Tiller Foundations guides above. The transactions sheet features validation on the category column which helps prevent invalid categories from being assigned. To reduce the manual effort required for categorization (which, let’s be honest, can be a lot), Tiller offers auto-categorization which can automate repetitive categorization by allowing you to configure rules (e.g. if description contains “x” categorize as “y”). I offer CategorEase, an AI powered alternative which can accurately categorize the vast majority of your transactions straight out of the box. These tools significantly reduce the manual categorization effort required by you. While CategorEase can handle almost all your transactions, I believe there is still significant value in reviewing and reflecting on each transaction individually.

Once all your new data has been added and your transactions categorized, you must refresh all data in order for the dashboards and pivot tables to update.

To refresh your data, navigate to the Data tab in the menu and click “Refresh All” as shown below, or press ctrl+alt+F5:

Tagging Transactions

Tags are another dimension you can use to organize your transactions. These could be used for a holiday, a home renovation, tax write-offs, or anything else you want to be able to isolate later. Enter them in the Tags column of the Transactions sheet.

A transaction can carry more than one tag. Separate them with commas:

Wedding, Honeymoon

Spaces around the commas are ignored, so Wedding,Honeymoon and Wedding, Honeymoon mean the same thing.

Two things to keep in mind when naming tags:

• Don’t use commas inside a tag name. The comma is what separates one tag from the next, so a single tag named Renovation, Phase 2 is read as the two separate tags Renovation and Phase 2.
• A transaction can carry up to ten tags. This is a limit of how the tags are split apart behind the scenes (see DAX Calculated Tables).

Tags are rebuilt from your Transactions sheet during a data refresh, so a newly invented tag won’t appear in the slicers until you refresh.

Filtering by Tag

The Spending, Income, Cashflow, Year to Date Summary, and Cashflow and Networth Summary dashboards each have a Tags slicer. Select one or more tags to narrow the dashboard to transactions carrying them; select nothing to see everything. Unlike the Global Exclude Tag below, these slicers take effect immediately without a data refresh.

The Cashflow Dashboard and the Cashflow and Networth Summary Dashboard share a single Tags slicer, so a selection made on one applies to both. The Networth Dashboard has no Tags slicer, since account balances aren’t tagged.

You’ll also notice a (Blank) entry in the Tags slicers. That isn’t a real tag — it stands for every transaction with no tags at all. It’s there so your untagged transactions can be included or excluded deliberately, rather than silently vanishing the moment you touch a tag filter.

A tag that’s currently excluded via the Global Exclude Tag below still appears in the slicers, but with no transactions left behind it.

The Global Exclude Tag

Tag slicers are good for exploring, but they’re configured per dashboard and can easily diverge. When you want to ensure transactions associated with a specific tag are always excluded, use the Global Exclude Tag dropdown on the Settings sheet. This is a great way to quickly exclude transactions from your dashboards and trends. See Excluding Transactions.

Pick a tag and every transaction carrying it disappears from every dashboard in the workbook. The per dashboard Tags slicers carry on working normally on whatever is left.

The dropdown offers three kinds of choice:

• (None) — no global exclusion. This is the default.
• Any of your tags — excludes every transaction carrying that tag.
• (Blank) — excludes every transaction that has no tags at all. Useful if you’ve deliberately tagged everything you care about and want to ignore the rest.

Only one tag can be globally excluded at a time, and changes require a data refresh to take effect (see Updating Data).

Excluding Transactions

Often, our financial data contains numerous transactions that we do not want to include in our analysis. The workbook gives you three ways to handle this, from broadest to most targeted:

1. Categorize it as a “Transfer”. Any category whose Type is Transfer is left out of the income and spending analysis. This is the right choice for money moving between your own accounts.
2. Hide the category from reports. Tick “Hide From Reports” against a category in the Categories sheet to keep it in your data but out of the dashboards.
3. Tag it and globally exclude the tag. Best for one-off situations that don’t deserve a category of their own — see The Global Exclude Tag.

Transactions you want to exclude often come in twos (the income transaction and the spending transaction) so make sure you exclude both!

Configuring Moving Average Trendlines

Some of the dashboards feature moving average trendlines to help you understand your data. These trendlines are created by averaging the values over a configurable number of prior periods, and thus may need to be adjusted based on the amount of data you have. As you accumulate more data you will likely want to increase the averaging window to determine longer term trends.

The period over which these averages are calculated can be configured by right clicking on a chart, selecting “Format chart area” and then selecting the trendlines from the chart options dropdown as shown below.

Then, select “Trendline Options” and modify the period as shown:

Troubleshooting

Error Validation

Due to the complexity of the workbook, various sheets include error validation to help prevent issues. Unfortunately, this error checking can’t be done automatically or on the entire workbook in one click so sheets have to be checked individually. To check a sheet for errors, navigate to the formulas toolbar and select error checking as shown below:

If you notice discrepancies in the dashboard, perform an error check on the Transactions sheet to catch invalid categories, then scan the Account ID column of the Balance History sheet for empty cells. A missing Account ID won’t show up as an error but will quietly drop the row from your dashboards, since the workbook builds each account’s unique identifier from it. Fill in any blanks and refresh.

Invalid or Missing Accounts

The dropdown that shows up when you try to add a new account is created and validated using the Balance History records. If you are trying to add an account and it isn’t showing in the list, or you have a validation error on an existing account, likely the problem is that there isn’t an associated record in the balance history sheet.

To solve this issue, simply create a balance history record for the account you want to add. It should then show up in the dropdown list and not throw any validation errors.

Missing or Incorrect Data

This can happen for a variety of reasons. I’ve listed some of the common causes and solutions below.

New Tags Not Appearing

The workbook rebuilds its list of tags from your Transactions sheet on every data refresh, so a tag you’ve just started using will only appear in the Tags slicers and the Global Exclude Tag dropdown after you refresh.

If a new tag still isn’t showing up:

• Check you’ve refreshed. Tags are rebuilt during a refresh, not as you type (see Updating Data).
• Check your slicer selections. If you previously selected specific tags in a Tags slicer, that selection is remembered, and a newly added tag won’t be part of it. Clear the slicer filter to bring everything back.
• Check for a stray comma. A comma inside a tag name splits it in two, so Renovation, Phase 2 appears as Renovation and Phase 2 rather than the single tag you intended.

Data Refresh Errors

Data Refresh errors indicate an issue with the Excel Data Model or a PivotTable. These can also happen for a variety of reasons. Sometimes the built-in Excel error messages are useful and highlight the exact issue, but sometimes they’re so ambiguous as to be effectively useless. Sometimes, data refresh issues can be solved simply by doing another data refresh!

General Tips

Since these errors often don’t provide any details the only way I’m aware of to resolve them is to methodically narrow down either the change that triggered the error or the specific pivot table / data model table in which the error is occurring and then experimenting with solutions until the issue is resolved.

To address the error by narrowing down the change that triggered it I generally follow these steps:

1. Undo the failed data refresh / restore the spreadsheet to a state prior to the error occurring
2. Introduce the desired changes to the spreadsheet incrementally, performing a data refresh after each change until the error occurs. This tells you which change caused the error.
3. Inspect the change to see if anything sticks out. Things to look for are differences in cell value formatting, missing values, other errors, etc.
4. Experiment with different ways of making the desired change, refreshing after each attempt until the spreadsheet refreshes successfully without errors.

To address the error by narrowing down the specific table in which the error is occurring I generally follow these steps:

1. Undo the failed data refresh / restore the spreadsheet to a state prior to the error occurring
2. Introduce the desired change
3. Troubleshoot the Excel Data Model
   1. Open Power Pivot and the Excel Data Model (see Advanced Functionality)
   2. Refresh each data model table one by one and see if the error occurs
   3. If an error occurs, hopefully it provides some information as to what the problem is. If not, inspect the source table looking for differences in cell value formatting, missing values, reference errors, etc.
4. If the data model refreshes successfully, you need to start troubleshooting all the pivot tables in the workbook.
   1. Unhide all Intermediate Sheets containing pivot tables
   2. Refresh the pivot tables one by one until the error occurs. This tells you that this pivot table is the issue (or is one of multiple with issues)
   3. Identify the data source of the pivot table
   4. Inspect the data source to see if anything sticks out. As always things to look for are differences in cell value formatting, missing values, errors, etc.

I’ve done what I can to reduce the likelihood of these kinds of errors but they will inevitably continue showing up due to the limitations of Excel and the approach I’ve taken. As more people use this and run into problems I will continue trying to make the workbook more robust.

Column Contains Blank Values

This error will occur if you delete an entry in your Accounts or Categories Foundation Sheets without removing the corresponding table row. Due to the way the data model links the values in these sheets, these tables cannot contain blank values.

To resolve the issue, right click on the empty row (you can tell the row is part of the table if the “Table Design” tab is shown at the top of the screen while the row is selected), hover over Delete, and click “Table Rows” as shown below. You should then be able to refresh all data as usual.

PivotTable field name invalid

This error can occur when data referenced by a pivot table changes in some way. Since the error does not provide any details the only way I’m aware of to resolve this error is to methodically narrow down the issue using the general troubleshooting steps described above.

Advanced Functionality

The following sections describe some of the more advanced functionality behind the spreadsheets for anyone interested in a deeper understanding or who are interested in modifying and extending the workbook.

Power Pivot and the Excel Data Model

The Excel Data Model allows you to conveniently reference multiple tables at once as a single data source. This allows for the creation of pivot tables that can draw data from multiple tables.

When using the Data Model, it’s highly beneficial to have Power Pivot installed. Follow the instructions provided by Microsoft.

Once installed, navigate to the new Power Pivot toolbar and select “Manage”

A window will pop up displaying the tables included in the data model. Alongside the four Foundation Sheets you’ll find five supporting tables:

The last two are deliberately left unrelated to every other table. They’re parameter tables — a way of getting a single value from a cell on the Settings sheet into a calculation, rather than dimensions you would ever filter by.

Once added to the Data Model, a pivot table can be created from a data model by navigating to Insert => PivotTable => From Data Model. This pivot table will have access to all the columns in the data model.

The Data Model also provides the ability to add custom columns that can include some basic formulas (unfortunately many Excel formulas are not supported here). This turned out to be the only way I could find to perform calculations on pivot table columns since the standard calculated field feature is disabled when using the Data Model.

These formulas are written in DAX rather than in regular Excel formulas. The three sections that follow cover the DAX in the workbook: the tables generated by it, the columns calculated with it, and the measures built on top.

DAX Calculated Tables

Three of the data model tables aren’t loaded from a worksheet at all. They’re generated by a DAX query that runs during a data refresh. In Power Pivot they look like any other table, but their contents are computed from the tables around them rather than typed in.

To see or edit the query behind one of these tables, navigate to the hidden sheet containing the table, right click on the table and choose Table => Edit DAX.

MasterCalendar

EVALUATE
CALENDAR(
    MIN( MIN(Transactions[Date]), MIN(BalanceHistory[Date]) ),
    MAX( MAX(Transactions[Date]), MAX(BalanceHistory[Date]) )
)

A continuous row of dates from your earliest record to your latest, with no gaps. This is a standard technique for time based reporting. Without it, a month in which nothing happened simply wouldn’t exist in your data, and charts would step straight over the gap as though it weren’t there. It allows account balances to carry forward to future months even if they don’t get updated. It also gives the averaging calculations something to count.

Tags

Excel has no native concept of several tags in one cell. Vacation, Japan 2026 is just a string, and a pivot table treats it as a single value entirely unrelated to Vacation. The Tags table solves this by splitting that string into one row per transaction per tag:

EVALUATE
VAR TagRows =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                SELECTCOLUMNS (
                    Transactions,
                    "Transaction ID", Transactions[Transaction ID],
                    "Tags",          Transactions[Tags]
                ),
                UNION (
                    ROW ( "Idx", 1 ), ROW ( "Idx", 2 ), ROW ( "Idx", 3 ),
                    ROW ( "Idx", 4 ), ROW ( "Idx", 5 ), ROW ( "Idx", 6 ),
                    ROW ( "Idx", 7 ), ROW ( "Idx", 8 ), ROW ( "Idx", 9 ),
                    ROW ( "Idx", 10 )
                )
            ),
            "Tag", TRIM ( PATHITEM ( SUBSTITUTE ( [Tags], ",", "|" ), [Idx], TEXT ) )
        ),
        [Tag] <> ""
    )

VAR BlankRows =
    SELECTCOLUMNS (
        FILTER (
            Transactions,
            ISBLANK ( Transactions[Tags] ) || Transactions[Tags] = ""
        ),
        "Transaction ID", Transactions[Transaction ID],
        "Tags",           "",
        "Idx",            1,
        "Tag",            "(Blank)"
    )

RETURN
    UNION ( TagRows, BlankRows )

Working outwards from the middle: CROSSJOIN pairs every transaction with the numbers 1 through 10, SUBSTITUTE swaps the commas for the pipe characters that PATHITEM expects, and PATHITEM pulls out the nth tag for each of those ten rows. TRIM removes any spaces left around the result, and the surrounding FILTER discards the rows where there was no nth tag to find. A transaction with two tags therefore produces two surviving rows and eight discarded ones.

Those ten ROW entries are where the ten tag limit comes from. If you genuinely need more, add further ROW ( "Idx", 11 ) lines to the UNION.

The BlankRows half handles transactions with no tags at all. They’d otherwise be absent from the table entirely, and any tag based filter would silently drop them. Instead they’re given a synthetic tag of (Blank) so they stay visible and can be included or excluded on purpose.

TagsGlobal

EVALUATE
DISTINCT ( Tags[Tag] )

The distinct list of tags currently in use. This is what the Tags slicers display, and what feeds the ExcludeTagList named range on the Settings sheet behind the Global Exclude Tag dropdown.

Calculated Columns

Calculated columns are worked out once during a data refresh and stored alongside the rest of the table’s data. That’s the reason several settings in this workbook only take effect after a refresh: the value has already been baked into the column rather than being calculated as you look at it.

Transactions

Amount(+)

IF ( RELATED ( 'Categories'[Type] ) = "Expense", -[Amount], [Amount] )

Your Transactions sheet stores spending as a negative number, which is correct but awkward to chart. This column flips the sign on anything categorized as an expense so that income and spending are both positive, and every chart in the workbook can be read as “bigger bar, more money”. Note that it depends on the category’s Type, so a transaction with a missing or misspelled category won’t be classified correctly.

Amount (+,Adj.)

[Amount(+)] * POWER ( ( 1 + 'InflationRate'[Average of Inflation Rate] ), YEAR ( TODAY() ) - YEAR ( [Date] ) )

The inflation adjusted amount, and the figure nearly everything in the workbook is ultimately built from. It compounds your inflation rate forward from the year of the transaction to the current year, so that at a 3% inflation rate a $100 purchase five years ago is adjusted to roughly $116 in today’s dollars.

Two consequences are worth knowing. Because this is a calculated column, changing the inflation rate on the Settings sheet does nothing until you refresh. And because the adjustment is anchored to TODAY(), every figure in the workbook quietly rebases itself on the 1st of January each year.

IsPresentMonth

AND ( [Date (Month Index)] = MONTH ( TODAY() ), YEAR ( [Date] ) = YEAR ( TODAY() ) )

Flags transactions belonging to the current calendar month. This is what the Present Month slicer on the Settings sheet filters on.

DateOnly — TRUNC ( [Date] ). Strips any time component so the date can be matched against MasterCalendar.

Balance History

DateOnly — as above.

Unique Account Identifier

VAR accName = [Account]
VAR accNum  = [Account #]
VAR accID   = [Account ID]
RETURN
IF (
    LEN ( accID ) = 0,
    BLANK (),
    accName & " - " & IF ( LEN ( accNum ) > 0, accNum & " ", "" ) & "(" & UPPER ( RIGHT ( accID, 4 ) ) & ")"
)

Balance history records don’t carry a reliable key back to the Accounts sheet, so one is constructed from the account name, the account number, and the last four characters of the Account ID.

The LEN ( accID ) = 0 guard is why a missing Account ID causes rows to disappear rather than raise an error: a blank identifier matches no account, so those balances never reach the dashboards.

Master Calendar

• Year — YEAR ( [Date] )
• Month — FORMAT ( [Date], "MMM" )
• YearMonth — FORMAT ( [Date], "YYYY-MM" )

YearMonth is formatted this way deliberately: YYYY-MM sorts correctly as plain text, which a month name alone would not.

Measures

Measures differ from calculated columns in that they’re evaluated on demand, against whatever you currently have selected. Every slicer, filter, and timeline in the workbook feeds into them.

Measures live in the calculation area at the bottom of each table in Power Pivot. Click one to see its definition in the formula bar.

Amount by Tag

This is the workhorse of the workbook — six of the nine pivot tables are built on it — and it’s the most involved piece of DAX in the file. It answers a deceptively awkward question: what’s the total, given the tags currently selected and the tag currently excluded globally?

The difficulty comes from the shape of the Tags table. Because one transaction can produce several tag rows, naively summing across it would count a transaction with three tags three times over. The measure therefore works in transaction IDs rather than amounts:

1. Read the global exclude tag from the disconnected GlobalExcludeTag table, and collect the IDs of every transaction carrying it.
2. Take every transaction ID in the current filter context, remove the globally excluded ones, then remove any whose tags fall outside the current slicer selection.
3. Separate out the transactions tagged (Blank) and handle them independently, so that untagged transactions are added exactly once, and only when (Blank) is actually selected.
4. Sum Amount (+,Adj.) over the surviving set of IDs, returning BLANK() rather than zero when there’s nothing to show, so empty rows drop out of the pivot tables instead of displaying as $0.

The step that isn’t obvious is the TREATAS at the end. Because of the direction the relationships run (see Data Model Relationships), a selection in a Tags slicer doesn’t automatically reach the Transactions table. TREATAS is what pushes the surviving transaction IDs back across, applying them to Transactions[Transaction ID] as though they had been a filter all along.

Average Monthly Spend

Despite the name, this drives both the Spending By Group Average and Income By Category Average sheets.

Working out an average per month sounds trivial until you have to decide what to divide by. Dividing by twelve is wrong for a partial year. Dividing by the number of months in which this particular group had activity makes the groups incomparable, because every row would be divided by a different number.

Instead, the measure counts the months containing any financial activity at all, and divides everything by that:

VAR MonthsWithSpend =
    COUNTROWS (
        FILTER (
            VALUES ( 'MasterCalendar'[YearMonth] ),
            NOT ISBLANK (
                CALCULATE (
                    [Amount by Tag],
                    ALL ( 'Categories'[Group] ),
                    ALL ( 'Transactions'[Category] ),
                    ALL ( 'Categories'[Type] ),
                    ALL ( 'Categories'[Hide From Reports] )
                )
            )
        )
    )
RETURN
    DIVIDE ( [Amount by Tag], MonthsWithSpend )

The ALL calls are what make this work. Clearing Group, Category, Type, and Hide From Reports means the denominator is identical for every row of the table, so group averages stay comparable and still add up to the overall average. A category you only spend on twice a year is divided by every active month rather than by two, which is what you want when you’re budgeting.

Because the column axis on these sheets is the year, each year is counted separately, so a partial first or last year divides by its real number of active months rather than by twelve.

Two filters are deliberately left in place. Date is one, which is what produces the per year behaviour just described. Tag filters are the other, and they change what the number means: with no tags selected you’re seeing an average per month across the period, but with a tag selected you’re seeing an average per month in which that tag was active. Both are useful — they’re just different questions.

The present month isn’t cleared either, which is why leaving a partly complete month in the data drags these averages down. It adds a whole month to the divisor while contributing only part of a month’s spending.

Last Known Balance

Account balances are snapshots rather than events: an account has a balance every day, whether or not one was recorded that day. This measure walks back from the end of the selected period to the most recent balance actually recorded, and reports that.

Without it, a month in which an account wasn’t polled would show a networth of zero rather than an unchanged balance.

Average of Inflation Rate

AVERAGE ( [Inflation Rate] )

The InflationRate table holds exactly one row, so this simply retrieves the value you typed on the Settings sheet. The AVERAGE wrapper is there because a measure has to aggregate something — it’s the conventional way of reading a single value out of a parameter table.

Data Model Relationships

The data model contains six relationships. All are active, all are many to one, and all are single direction. You can view them in Power Pivot via Design => Manage Relationships.

GlobalExcludeTag and InflationRate are connected to nothing at all, by design. They exist to carry a single value from the Settings sheet into the calculations, and the measures that need them read them directly.

Single direction means filters travel from the “one” side to the “many” side and not back again. Selecting a category filters your transactions; selecting a transaction does not filter your categories. This is the sensible default, and it keeps refreshes fast and behaviour predictable.

It does have one consequence worth understanding, because it explains the most complicated code in the workbook. Filters flow from Transactions into Tags, not the other way around, which means selecting a tag in a slicer does not on its own filter your transactions. Reversing the relationship would introduce ambiguity elsewhere in the model, so instead the Amount by Tag measure resolves the tag selection down to a set of transaction IDs and applies them to the Transactions table explicitly with TREATAS. The relationship shapes the data; the measure does the filtering.

Dynamic Chart Ranges and the Name Manager

While Pivot Charts update automatically when their underlying data changes, standard charts referencing a range of cells do not. However, standard charts are much more flexible than Pivot Charts and were required in multiple places in order to display data in the desired way. To enable standard charts to update their data automatically, you can create dynamic formulas to automatically retrieve the necessary data, and then store these formulas in the Name Manager which can subsequently be referenced anywhere in your workbook (including charts!)

You can see all the named formulas in the workbook by navigating to the Formulas toolbar, then clicking “Name Manager”:

To see how these named references are wired up to the charts, you can right click on a standard chart (you can determine if a chart is a pivot chart if the “Pivot Table Analyze” toolbar shows up when you select it), click “Select Data”, and then edit one of the dynamic data series:

So in the above example, the “Spending” series values in the Cashflow and Networth summary chart is set to ='Cashflow and Networth Analysis.xlsx'!DynamicSpendingMonthly (you also need to reference the workbook when referencing names from charts for some reason). This points to the named reference DynamicSpendingMonthly, which you can see from the Name Manager points to the formula =GetDynamicRange('Income and Spending Monthly'!$H$7, 'Income and Spending Monthly'!$H:$H, 1) (all cell references in the name manager must be absolute references i.e. $H must be used in place of H). Now, there’s still one more layer of indirection: GetDynamicRange isn’t a built in Excel function, it’s a Custom Function.

It’s worth noting that the process of referencing named formulas in charts can be very fussy. For example, Excel will throw an annoying error (which doesn’t even seem legitimate since everything seems to work fine) if you try to reference a name in a chart that refers to anything more complex than a single function (for example, a function multiplied by -1, or two functions added together). Incorporating the additional calculation into the source data to keep the named reference simple resolves the issue.

Custom Functions

Custom functions are extremely useful for reusing complex formulas. There are likely multiple ways to create custom functions, but the method I used was through Excel Labs.

Once installed, you can open the Excel Labs pane by navigating to the Home toolbar and clicking Excel Labs on the far right:

From here, select “Modules” to see the GetDynamicRange custom formula:

Troubleshooting Dynamic Charts

Despite my best efforts, I still occasionally find issues with the dynamic charts in the dashboard sheets. Often these are due to small changes in pivot table filters which can mess up the dynamic range formulas described above. These formulas determine the cells of interest by looking at the number of cells in a column containing values minus a manual offset, so if you add a new cell containing a value to said column (like a new pivot table filter) then you need to subsequently increase the offset. To help troubleshoot these issues, each dashboard features a hidden table depicting a mirror of the formulas used to generate the series values. The table displays the formulas used by the chart and their results as shown below:

This makes it much easier to identify inconsistencies in data and which formula and underlying Intermediate Sheets are creating the issue. To see these tables, simply unhide the columns to the right of the gray sections of the dashboards and re-hide them when you’re done.