Multiple budgets for different financial years in the same spreadsheet. How to filter on FY?

[ashas]

Hi Folks,

I have created a personal finance dashboard that i'd like to filter by financial year..  so far it works well. 

I have a budget table (see below) for each category and category type that i'm tracking:

As we approach the end of the financial year, I want to update this budget table with my new budget for each category for the next financial year.... so I realised I would need to update this table with the financial year and new budget (whilst also maintaining the old information)

So I think I need to update the budget spreadsheet like this.. so it shows me the budget for all years as I build it up..

My data model looks like:

My question is:  

1) Is what i'm thinking the right way to do this?

2) How do I filter the budget table based on the appropriate FY column ?  

I knew I'd have this issue but kinda pushed it out as was in the too hard basket 🙂 Any feedback appreciated and also if i've gone about this the wrong way, happy to take feedback.

Thanks,

Asha

[ashas]

Hi @BA_Pete ,

Thanks for that - it seems to have worked but i've broken a couple of other things - that i'm trying to work out.

Just to check, is this what the CAtegory table should look like:   

My data model looks like this now..

I had a measure in the Transactions table that would calculate the average budgeted amount for each category... the measure is:

Budgeted spend per month = AVERAGEX(VALUES(Dates[MonthName]),[Categories allocated budget])

Categories allocated budget = CALCULATE([Days In Date Context]/[Days In Year]*SUM(budget[Amount]))

So, this measure still works..

However, I also had a measure in the Transactions table that would give me the Actual average spend per month:

Average spend per month = AVERAGEX(VALUES(Dates[MonthName]),[ABS Amount])

ABS Amount = ABS(SUM(Transactions[Amount]))

Now this was working fine but since making the changes it's not giving the correct number when I add this measure into a visual table (example below) - budgeted spend per month is correct but the average spend per month is incorrect

 I know it has to do with context and relation to the category but not sure how to fix this? 

Thanks,

Asha

[BA_Pete]

Hi Asha,

Assuming you are using your Dates table to filter/slice the page, you could calculate average monthly spend something like this:

_avgMonthlySpend =
VAR __noofMonths = DISTINCTCOUNT(Dates[MonthName])
RETURN
DIVIDE([ABS Amount], __noofMonths, 0)

Pete

[BA_Pete · Accepted solution]

Hi Asha,

What you're essentially doing here is changing your Budget dimension table into a Slowly Changing Dimension (SCD) table. Based on the simplicity of your use-case, I'd recommend setting this up as a basic Star Schema model, with the Budget table being its own fact table (not related to the Transactions table).

So, the Budget table:

Delete the relationship between Transactions and Budget.

In Power Query: change the column names from 'FY23' and 'FY24' to the actual year end date e.g. 31/03/2023 and 31/03/2024.

Multi-select (Ctrl+click) [Category Type] and [Category] and go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.

This makes it really easy to add new columns for new years, but also changes the table into the correct structure for reporting regardless of how many new year columns you add.

You will now see that you have a column that is just year end dates, which can be related to your Dates table.

The rest of the model:

Create separate dimension tables for each dimension common between Budget and Transactions.

For example, to create a Category dimension table in Power Query you would create a new blank query, then paste in this code:

let
    Source = Table.Distinct(Table.SelectColumns(Budget, "Category"))
in
    Source

Once this new table is sent to the model, you would relate it to your tables as follows:

dimCategory[Category] ONE : MANY Budget[Category]

dimCategory[Category] ONE : MANY Transactions[Category]

Do the same with your Dates table relating to both tables like this, and you have the basic model structure started.

The measures:

You would create basic measures like this:

_budgetValue = SUM(Budget[Value])

_spendValue = SUM(Transactions[Amount])

Now you can add dimensions from your common dim tables and these measures to any visuals and they will relate correctly across date/category etc.

For example, once you've done the above, try adding Dates[FY], dimCategory[Category], [_budgetValue], and [_spendValue] into a table visual. You'll see that all the different date/category/budget/actual values auto-aggregate into the correct places.

Pete