Split data into monthly budget figure

[kennoe]

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

[Ashish_Mathur]

Hi,

Even though you have shared the PBI file, i cannot understand your data/question.  Please share/explain your business question/data more elaborately.

[kennoe]

At its simplest, I want to:

1. 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)
2. Find the earliest start date and latest completion date for a project in the IR_Example41 table. (16/10/2017 and 30/06/2019)
3. Work out the duration for 2. above. (20.7mths)
4. 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_Mathur]

Hi,

This question takes time to solve.  Please refer to a similar question which I have solved here - Distribute projected revenue annually.

Hope this helps.