Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ashas
Frequent Visitor

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

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:

ashas_2-1683275379045.png

 

 

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..

ashas_3-1683275525587.png

 

My data model looks like:

 

ashas_4-1683276118456.png

 

 

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
ashas
Frequent Visitor

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:   

ashas_0-1683352557836.png

 

My data model looks like this now..

ashas_1-1683352594421.png

 

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
 
ashas_2-1683353032300.png

 

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

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors