Multi year budgets v actual

[arumel]

Hi all, new to power BI and need some help with the intial setup for a dashboard - Essentially this is a budget to expense report.

I have 2 fact tables:

1: Budget = Budget data is on an annual basis broken down by departments and sub departments

2: Transactions = The expense transactions are on a daily basis

At any given time i want to compare the actual expenses for a department or sub department to its annual budget - i.e in 2022 i want to compare the annual budget for Department A (700 in total) to the actual expenses from the daily expenses .. i would also like to further break this down and see the sub department budgets to their expenses.

This is my initial setp up:

 I am having issues around comparing annual budgets to daily transaction amounts and then slicing by departments or sub departments.

Any advice on how to struture my fact tables and create the relationships will be much appreciated.

Thanks in advance

[arumel]

Hi all,

The above soluton works when the "Year" table is created as mentioned, however if i try and create a 'Dates' tables as a autocalendar and try and create a relatationship bertween the new 'dates' table 'Year' to the year columns in 'budget' and 'actual' tables, i get error meassages:

I would prefer a 'dates' table to to year on year comparisons ..

Any solutuions to these problems?

Thanks again

[v-yiruan-msft]

Hi @arumel ,

Thanks for your feedback. That's only warning message due to both of the related tables(the table 'Dates' and 'Budget') has duplicated values. You can ignore it and click "OK" button to create the relationship for them directly.

Best Regards

[danextian]

Hi @arumel ,

The annual budget can be split into several periods. The question now is what periods (days, months?) it is going to be split into? Also, provide a workable sample data ( not an image) that can be copy pasted into Excel or you can provide a link to an Excel file in the cloud.

[arumel]

Hi, I only want to compare the annual budget to the expenses for that year .. i.e only need to compare the total 2022 budget to the total 2022 expense for a deprtment or sub department (dont need to brekdown to monthly budget details).

Below are the tables copied:

Deaprtment	Sub Department	Department Code	Year	Budget
A	A1	A1.1	2021	100
A	A2	A2.2	2021	150
A	A3	A3.3	2021	200
A	A4	A4.4	2021	120
B	B1	B1.1	2021	175
B	B2	B1.2	2021	180
B	B3	B1.3	2021	300
B	B4	B1.4	2021	180
A	A1	A1.1	2022	160
A	A2	A2.2	2022	200
A	A3	A3.3	2022	240
A	A4	A4.4	2022	100
B	B1	B1.1	2022	160
B	B2	B1.2	2022	145
B	B3	B1.3	2022	300
B	B4	B1.4	2022	200
A	A1	A1.1	2023	200
A	A2	A2.2	2023	260
A	A3	A3.3	2023	170
A	A4	A4.4	2023	150
B	B1	B1.1	2023	220
B	B2	B1.2	2023	175
B	B3	B1.3	2023	250
B	B4	B1.4	2023	220

Trans Date	Order No	Vendor	Currency	Amount	Department Code
1/1/2021	1011	1	USD	25	A2.2
1/2/2021	1011	1	USD	41	B1.1
5/8/2021	5055	2	USD	74	A2.2
9/9/2021	9099	3	USD	12	A3.3
4/4/2022	4044	2	EUR	36	A4.4
6/9/2022	6066	1	EUR	11	B1.1
1/12/2022	1211	2	EUR	36	B1.2
1/3/2023	1011	3	GBP	45	A1.1
1/4/2023	1011	4	GBP	93	A2.2
9/9/2023	9099	3	USD	44	A3.3

Thanks for your help

[danextian]

Hi @arumel ,

In your Actuals table, add a year column either using M or DAX. Then create a Year dimensions table and a Department dimension table. Relate both dimension tables to the fact tables. Use the dimension columns from the dimensions table in your visuals.  Please see attached pbix for details.

[arumel]

Thanks for your help .. im going to have a play with this tonight .. will let you know how i get on

Regards

[v-yiruan-msft]

@arumel  Thanks for your contribution on this thread.

Hi @arumel ,

Have you got the solution? If yes, could you please mark the helpful post as Answered? Thank you.

Best Regards