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

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