Forum Discussion
Helper ISplit data into monthly budget figure
We set the budget for each project at the start of the work. We record actual expenditure monthly. We do not however, show the budget as a monthly amount against which we compare the actual expenditure.
I would like to create a column in the relevant table that has the project budget split into monthly amounts, based on the number of months planned for the project. This is so that I can create a line chart that compares cumulative actual expenditure against budget.
Example data can be found at: https://www.dropbox.com/s/sxukjfske24rmi8/Example43.pbix?dl=0
Thanks
Steve
3 Replies
- Ashish_Mathur
Super User
Hi,
Even though you have shared the PBI file, i cannot understand your data/question. Please share/explain your business question/data more elaborately.
kennoeAt its simplest, I want to:
- Add up all the amounts that correspond to a "Funding Status" of "Released" in the IF_Example41 table. This is the total budget for all for the projects in the data set. ($7,545,616)
- Find the earliest start date and latest completion date for a project in the IR_Example41 table. (16/10/2017 and 30/06/2019)
- Work out the duration for 2. above. (20.7mths)
- Divide the total calculated in 1. above by the duration calculated in 3. above to get a monthly budget figure for all projects in the data set. ($363,936)
I can then use this data to compare the monthly budget against the actual expenditure.
This is not a very accurate representation of the real situation. To make it accurate, I would need to only include the budget for projects when they are "Active". So the budget figure would be adjusted as projects start and finish.
Is this clearer. I have a good picture of what I want to do in my head. Explaining it in writing is a bit more difficult.
Thanks
Steve
- Ashish_MathurHi,This question takes time to solve. Please refer to a similar question which I have solved here - Distribute projected revenue annually.Hope this helps.
