cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors