Dealing with budgets

[mlsx4]

Hello everyone!

I've got a problem dealing with budgets. They're all defined in Excel files. One sheet per center (about 20 centers), one workbook per year (I'm reading the year using the file name). Up to now, we hadn't had any problem. But now, we need to update the budget for one of the centers, but we need to keep the previous one from January to October. 

How can I deal with it? If I have to start from scratch it is OK for me (even though, I think that DAX formulas would need a rewriting), but I need a solution with enough flexibility to be able to manage all these changes.

Any idea of how to do it?

[v-zhangti]

Hi, @mlsx4 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlsx4]

Hi @v-zhangti 

That's one of my Excels (I have remove a lot of rows and numbers may not have sense because they're randomly generated). I have one per year so: Budget 2023.xlsx, Budget 2024.xlsx, and so on in the same folder. Then, inside the example file there are only two centers, but normally I have over 20 centers.

Accounts are composed in this way (xxx-cc-....) where xxx are the codes for Spanish account, cc match with the center's number and the rest are generated from 1.

In Power BI, when I read all these files, I perform a merge to have only one table with this structure in columns:

• Year: which is got by reading the filename and removing "budget"
• Center: which is read from getting two first characters of the Excel sheet name.
• Accounting account
• Description
• Monthly Budget
• Daily per occupation
• Monthly per occupation

My problem is that with this structure, I cannot deal with changes in budget. For instance, if I now need to change incomes2 for center1, I will have to overwrite the value, but I don't have any possibility to keep previous value up to October. 

Is there any workaround? Or it is just impossible? I was thinking about any way of keep track of starting date for budget and the date on which is changed but I don't want to replicate all budget just for a change in a line...

I hope to have explained myself!

Thanks in advance.

[mlsx4]

@v-zhangti have you found anything yet?