Budget per period via entered data

[Anonymous]

Hi,

I would like some help with Power BI. I have created a table with the following data:
Department | Period | Invoiced amount period | Budget

Department	Period	Invoiced amount period	Budget
Projecten E en W	202101	200.000,00	250.000,00
Projecten E en W	202102	125.000,00	100.000,00
Projecten E en W	202103	350.000,00	250.000,00
Service en Onderhoud	202101	225.000,00	300.000,00
Service en Onderhoud	202102	325.000,00	325.000,00
Service en Onderhoud	202103	350.000,00	125.000,00
Stadsverwarming	202101	120.000,00	160.000,00
Stadsverwarming	202102	230.000,00	250.000,00
Stadsverwarming	202103	211.000,00	210.000,00

Above is how I would like it to be.

In this last column I would like to show the budget of that period in question, per department.

The budgets are imported manually via an Excel file. There is no relationship between this table and other tables.

When I now add the budgets to the table I have compiled, the correct budgets are not linked to the correct period/department and I suddenly see many more lines (all periods per department, but not matched with the correct budget)

I don't understand how I can correct this.

Thank you for the help!

[Anonymous]

Hi Nicolett,

The reason you are seeing lot of records is because there is no relaitonship. For your solution to work, you would need follwing relationships in the model.

1. Department - Make sure the Department Names are same as in the Department table (if there is one).

2. Period - you may have to add another field called PeriodFirstDate or you can enter the Peiod in YYYYMMDD format. For example: 

period: 202101; PeriodFirstDate: 20210101

202102 will have 20210201 and so on.

Then join the PeriodFirstDate with your Date or Calendar table.

Make note when you build reports - the granularity of the budget is at Department and Month - therefore they will repeat the same value when you go to a lower granularity (like daily data) or different dimension (like products or services).

Let me know if you need more help or share your PBIX file.