Using Mint.com and Power BI to Visualize Your Personal Finances

This article provides some additional detail beyond what I provide in this video:

Update 1/29/19: Note that I've now released a second version of this Power BI file, and I cover details here.

Deeper Dive

Let’s look at some of the features of the visualizations covered in the video in more detail, including dumping the text for many of the DAX expressions, allowing you build your own .pbix file from scratch, if you want to go that route (you can also grab the final version of it from here).

Starting with the Date table, that’s defined as:

Date = ADDCOLUMNS (

CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),

"PrevYearDate", DATE(YEAR([Date])-1, MONTH([Date]), DAY([Date])),

"DayOfMonth", FORMAT( DAY([Date]), "00" ),

"Is T6M", (YEAR([Date]) * 12) + MONTH([Date]) >= (YEAR(TODAY()) * 12) + MONTH(TODAY()) - 6 && (YEAR([Date]) * 12) + MONTH([Date]) < (YEAR(TODAY()) * 12) + MONTH(TODAY()),

"Is T6M Inclusive", (YEAR([Date]) * 12) + MONTH([Date]) >= (YEAR(TODAY()) * 12) + MONTH(TODAY()) - 6 && (YEAR([Date]) * 12) + MONTH([Date]) <= (YEAR(TODAY()) * 12) + MONTH(TODAY()),

"Is Current Month", FORMAT ( [Date], "YYYY/MM" ) = FORMAT ( TODAY(), "YYYY/MM" ),

"Is Previous Month", (YEAR([Date]) * 12) + MONTH([Date]) = (YEAR(TODAY()) * 12) + MONTH(TODAY()) - 1,

"Is Last 3 Years Inclusive", YEAR([Date]) >= YEAR(TODAY()) - 2 && YEAR([Date]) <= YEAR(TODAY()),

"Is Current Year", YEAR([Date]) = YEAR(TODAY())

)

Some of the columns are self-explanatory – the more interesting ones use “TODAY” to establish relative-to-now time ranges. (These are especially useful as filters.) For example, “Is T6M” represents “is the date in the trailing 6 months” – yes or no.

A calculated measure on the date table includes:

Count of Months = CALCULATE(DISTINCTCOUNT('Date'[Year Month Number]), FILTER('Mint Transactions', [Date]))

This measure takes dates from your transaction list and counts the number of months the range contains. For example, if you were filtered to a year’s worth of transactions, expect the measure to return “12” if there were transactions in every month. And:

Days Between = DATEDIFF(MIN([Date]), MAX([Date]), DAY) + 1

This allows us to do daily averages by taking an amount and distributing it over all calendar days that exist between the start and end date of the current range.

To do year-over-year comparisons, let’s add a “Previous Year” amount, which is a sum over our base Amount field, but filtered based on [Previous Year Date] (PrevYearDate on the Date table):

PY Amount = CALCULATE (

    SUM ( 'Mint Transactions'[Amount] ),

    FILTER (

        ALL ( 'Date' ),

        CONTAINS (

            VALUES ( 'Date'[Previous Year Date] ),

            'Date'[Previous Year Date],

            'Date'[Date]

        )

    )

)

To build a full-year forecast amount, let’s determine the latest date of Mint data that we have using this measure:

Last Available Data Date = CALCULATE ( MAX ( 'Mint Transactions'[Date] ), ALL('Date'), ALL('Mint Transactions'))

Here we’re removing all other filters to look at the maximum transaction date regardless of context. Our full year forecast amount becomes:

Full Year Forecast Amount = SUMX('Mint Transactions', IF([Date] <= [Last Available Data Date], [Amount], 0)) + SUMX('Date', IF([Date] > [Last Available Data Date], [PY Amount], 0))

This uses SUMX to iterate where we can in the first case choose amounts that are coming from dates less than or equal our last available data date; in the second case it’s picking the previous year amount for dates greater than our last available data date. As discussed in the video, this gives us “future dated” data that’s drawn from the prior year’s data set, to simulate the remainder of the year.

Our year-over-year amounts are built incrementally:

YoY Amount = SUM([Amount]) - [PY Amount]

YoY % = DIVIDE([YoY Amount], [PY Amount])

Forecast YoY Amount = [Full Year Forecast Amount] - [PY Amount]

Forecast YoY % = DIVIDE([Forecast YoY Amount], [PY Amount])

Forecast YoY % (CY) = CALCULATE([Forecast YoY %], 'Date'[Year] = YEAR(TODAY()))

So far, we’ve been dealing with “raw amount”, which is unsigned. This means debits and credits can’t easily be combined: to do so we need to look at the [Transaction Type] column of our Mint transactions. A signed amount calculated column does that for us:

Signed Amount = IF([Transaction Type] = "credit", 1, -1) * [Amount]

We can also make use of a second calculated column that can be used to analyze signed amounts for days of the month that exceed the day of the month of the last day of available data.

Signed Amount After Today Day = IF(DAY([Date]) > DAY([Last Available Data Date]), [Signed Amount], 0)

This, of course, only makes sense in months prior to the last month of data available, where we can for example get an average of spending over the last six months – by the day of the month… which leads to a specialized calculated table:

DaysOfMonthReporting T6M = SUMMARIZE('Date', [Day Of Month],

     "Sum Signed Amount",

           CALCULATE(SUM('Mint Transactions'[Signed Amount]),

                FILTER('Date', [Is T6M]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes")),

     "Avg Signed Amount",

           CALCULATE(AVERAGE('Mint Transactions'[Signed Amount]),

                FILTER('Date', [Is T6M]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes")),

     "Avg Signed Amount per Day",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount]), 'Date'[Count of Months]),

                FILTER('Date', [Is T6M]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes")),

     "Avg Signed Amount per Day CM Remaining",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount]), 'Date'[Count of Months]),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes"),

                FILTER('Date', [Is T6M]=TRUE() && DAY([Date]) > DAY([Last Available Data Date]))),

     "Avg Signed Amount After Today",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount After Today Day]), 'Date'[Count of Months]),

                FILTER('Date', [Is T6M]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes")),

     "Avg Signed Amount After Today with Income",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount After Today Day]), 'Date'[Count of Months]),

                FILTER('Date', [Is T6M]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes" || [Category Exclude] = "Income")),

     "Avg Signed Amount per Day with Income",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount]), 'Date'[Count of Months]),

                FILTER('Date', [Is T6M]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes" || [Category Exclude] = "Income")),

     "Current Month Spend",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount]), 'Date'[Count of Months]),

                FILTER('Date', [Is Current Month]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes")),

     "Previous Month Spend",

           CALCULATE(DIVIDE(SUM('Mint Transactions'[Signed Amount]), 'Date'[Count of Months]),

                FILTER('Date', [Is Previous Month]=TRUE()),

                FILTER('Mint Transactions', [Category Exclude] = "Real Expenses" || [Category Exclude] = "Taxes"))

)

This is leveraged on the “Day of Month” and “Month Progress” pages, where the fact we’ve summarized our Date dimension by the [Day of Month] can be rendered both in tabular form and in a line chart. Cumulative measures sit on top of the calculated table, including:

Cumulative Avg After Today = CALCULATE(SUM([Avg By Day After Today]), FILTER(ALL('Date'), [Day Of Month] <= MAX('DaysOfMonthReporting T6M'[Day Of Month]))) 

Cumulative Avg Expense After Today = CALCULATE(SUM([Avg Expense By Day After Today]), FILTER(ALL('Date'), [Day Of Month] <= MAX('DaysOfMonthReporting T6M'[Day Of Month]))) 

Cumulative Avg Spend By Day = CALCULATE(ABS(SUM([Avg Expense By Day])), FILTER(ALL('Date'), [Day Of Month] <= MAX('DaysOfMonthReporting T6M'[Day Of Month]))) 

Cumulative Avg Total = CALCULATE(SUM([Avg Expense By Day with Income]), FILTER(ALL('Date'), [Day Of Month] <= MAX('DaysOfMonthReporting T6M'[Day Of Month]))) 

Cumulative Current Month Spend = CALCULATE(ABS(SUM([Current Month Expense])), FILTER(ALL('Date'), MAX('DaysOfMonthReporting T6M'[Day Of Month]) <= FORMAT(DAY([Last Available Data Date]), "00") && [Day Of Month] <= MAX('DaysOfMonthReporting T6M'[Day Of Month])))

Current Month Expense vs T6M = [Cumulative Current Month Spend] / [Cumulative Avg Spend By Day]

We can also use a cumulative signed amount, which is what we use on the “Net for CY” page, to show what could be considered a “balance” starting at zero on January 1^st of the current year:

Cumulative Signed Amount = CALCULATE(SUM('Mint Transactions'[Signed Amount]), FILTER(

           ALLSELECTED('Mint Transactions'[Date]),

           ISONORAFTER('Mint Transactions'[Date], MAX('Mint Transactions'[Date]), DESC)))

If you use “quick measures” in Power BI, you’ll see similar DAX generated when you elect to do a “running total.”

On the “Food” page, our card showing spending on food per day is determined based on a generic measure:

Avg Amount per Day = SUM([Amount]) / [Days Between]

The card is filtered to the “Food” category (group), and the interaction with other page visualizations supports date filtering – so if you click on a month in the clustered column chart, the card amount changes to apply to the selected month only.

To find the ratio of restaurant spending to total food spending, we use a custom measure that takes the specific category “Groceries” and divides it by the broad category group “Food”. This gives the percentage of non-restaurant spending, so we reverse this by subtracting it from 1.0 to get restaurant spending.

Restaurant Spending Ratio = 1.0 - DIVIDE(CALCULATE(SUM([Amount]), FILTER('Mint Transactions', [Category] = "Groceries")), CALCULATE(SUM([Amount]), FILTER('Mint Transactions', [Category Expenses] = "Food")))

Recall from the video that changing to use your own data is as easy as going to “Edit Queries” / “Data source settings”:

And then “Change Source”…

Something else to note: when you use your own data, your Mint categories may differ from mine, so you’ll need to review the category groups I’ve established and change to suit your taste. You can edit groups by using the ellipsis on the group in the field list:

Update 9/24/18: I’ve made a small “fix” related to the “days between” measure, below (it needs +1 to be inclusive). I’ve also added two more report pages to the .pbix file available for download.

One supports a more detailed view of “Category Detail” where individual vendors are brought into a stacked column chart, and drill-down has been used to show values by month. (Try doing a “drill-up” to see the bars change from “by month” to a “by year” summary.) Of note, my average line here is based on the page slicer so you can choose something other than “Shopping & Misc” which I made the default.

The “YoY Changes” page uses its play axis to show how categories have changed in their year-over-year ratio, over time. This can be useful to identify categories that are “moving to the right and/or upwards” which would indicate higher levels of spending.

A Word About Obfuscation

Some might wonder how I scrambled amounts in my Mint .csv file, or changed the low-level descriptions that would have normally given away the names of specific vendors. It was a multi-step process, leveraging xSkrape for Excel:

1.       Open the downloaded file in Excel. Here I’m showing three manually faked transactions:

2.       In J2, I enter the formula:

3.       Copy for all rows:

Notice how the value is the same when the values in column B are the same – so we’re preserving what could be an important trait in the data (e.g. cardinality), but the new description in column J is a hashed value. Hashes typically lose information from their original values, making them hard to “reverse engineer”, although not impossible. It’s also possible for two legitimate source values to return the same hash value, and C#’s GetHashCode is sufficient for what I’m trying to accomplish here.

4.       Select the cell range, Copy (Ctrl-C), and use Home -> Paste Special, pasting values only over the existing formulas.

5.       Replace Column B with Column J. Repeat for column C.

6.       Save.

Amounts used a similar concept, but I included some variability using Math.Random. This was a quick-and-dirty approach, where a lot more can be done using this add-in, as I describe here.

Conclusions

Keep in mind, I created six pages of potent reporting from one simple table of source data. More is certainly possible, and I’d be interested in hearing about your visualization ideas in this topic area which has a significant following (a market served by Mint.com, YNAB, QuickBooks, and more). In fact, the downloadable version of the demo file is something I’m likely to add more pages to, as I intend to use this for my own personal use as well – so check back often!

Also, keep in mind this demo has not been truly about budgeting: it’s about visualizing existing spending and income. Other tools exist that support budgeting, although technically we could introduce additional tables to support budgets, tracking actual versus budgeted, and so on.

If Mint changes their export file format, we may have to adjust the data mapping, but what I’d like to do is collect a slew of different formats for different institutions (including brokerages) and build a translation service that lets everyone build to a common schema. Alternatively, if there’s enough interest I’ll investigate using an aggregator service and build a complete app that gives you full control over how you want to see all your data. In fact, I started exploring this concept with WiserRobo, and may yet still bring it to market, powered at least partly by Power BI, if there’s enough interest!

Disclaimer

NEITHER MYSELF NOR THE INFORMATION PROVIDED ARE INTENDED TO PROVIDE LEGAL, TAX OR FINANCIAL ADVICE. I AM NOT A FINANCIAL PLANNER, BROKER OR TAX ADVISOR. The Services are intended only to assist you in your financial organization and decision-making and is broad in scope. Your personal financial situation is unique, and any information and advice obtained through this may not be appropriate for your situation. Accordingly, before making any final decisions or implementing any financial strategy, you should consider obtaining additional information and advice from your accountant or other financial advisers who are fully aware of your individual circumstances.