I am trying to work on a personal finance report and want to incorporate monthly budgets I set for myself.
I have created the data model as the following:
Transactions Table = Export from Bank transactions outlining day to day transactions with the vendor name
Category Mapping = Table that maps Names of vendors to a set amount of category
Budget = A set of categories, their budgeted number (monthly)
I have a one to many relationship on Vendor with Transactions and Category Mapping and a One to many relationship with Budget and Category mappings on the Category name.
So the dataset looks something like this:
Transactions:
Date | Vendor | Amount
Day x | Vendor x | Amount
Day x | Vendor z | Amount
Day y | Vendor y | Amount
Day z | Vendor B | amount
Category Mapping
Category Name | Vendor
Category Name 1 | Vendor x
Category Name 2 | Vendor B
Category Name 1 | Vendor y
Category Name 3 | Vendor z
Budget
Category Name | Budgeted
Category Name 1 | 100 (budget)
Category Name 2 | 200 (budget)
Category Name 3 | 100 (budget)
I am having a really hard time coming up with a measure that would show me the difference in the amount spent vs amount budgeted on a monthly basis. I know the data is on different granularities as the data in transaction is daily (and can have multiple days) vs data in the budget is just a single monthly value by category..and I just cant figure out how to relate the two.
So far this measure would show me the amount of spend by categories:
SpendByCategory = SUMX(VALUES('Category Mappings'[Category]),CALCULATE(SUM('Bank Export'[Amount])))
But how do i lookup the category and pull the amount that I need to subtract from the actual spend ?
