Running total from project budget minus monthly cost

[Anonymous]

Hey everyone,

I work at a construction company, and I'm trying to develop a visual for the burndown rate on each project. I have two datasets, one contains the budget information for each project (this dataset contains budgeted line items for each project and is for the most part stagnant), and the other dataset contains costs associated with the project (this dataset updates daily with new costs). I would like the visual to end up looking something like the chart below:

I can generate monthly cost totals easily enough, but I'm having trouble figuring out how to get a running total for the remaining budget (total cost - original budget total).  my two datasets contain the information below:

Budget Database:

Project ID	Original Budget
562949953522538	1862541
562949953588055	12563839
562949953563451	1263489
562949953611947	759362

Cost Database:

Project ID	Date	Cost Amount
562949953522538	4/12/22	5000
562949953522538	4/25/22	12000
562949953522538	5/13/22	100000
562949953588055	4/1/22	60000
562949953588055	5/1/22	18000
562949953563451	5/5/22	2500
562949953611947	5/12/22	7500
562949953611947	5/2/22	36000

I'm not sure if the best way to go about this would be through creating a calculated column in the cost database somehow or if I could do this all within a visual somehow (I haven't been able to find a way to accomplish that yet). Any help or direction would be greatly appreciated. 

Thanks! 

Cole

[SpartaBI · Accepted solution]

@Anonymous follow these steps:
1. Create a relatioshiop (one to many) between the 2 tables through the Project ID:

2. Create these measure:

$ Cost = SUM(Cost[Cost Amount])

$ Cost RT = 
VAR _current_date = MAX(Cost[Date])
RETURN 
CALCULATE(
    [$ Cost],
    Cost[Date] <= _current_date
)

Remaining Budget = SUM(Budget[Original Budget]) - [Cost RT]

3. Put the date and measures in the chart like this (this is the result we get from your sample data):

This is of course also repecting filters that you will choose from the Projcect ID column in the Budget Table (Filters by default go from the 1 side to the many side)

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

Hey SpartaBI,

Thanks for your response! I was able to recreate the measures you indicated above however I am getting the result below:

One problem I ran into was that my budget data set actually contains a row for each budgeted line item in a project, so I can't create a one to many relationship between those two datasets, would that be the reason I am getting this result? 

thanks in advance for your help! 

[SpartaBI]

@Anonymous not sure if that is the reason, I think more like you maybe using the wrong date column in the axis, but anyway we can check this. You can force a 1:1 to be 1:m in the relationship dialog. Please try that (and make sure the direction is from budget to cost like in my screenshop and update me after.
P.S  if the relationship is 1:1 than you should have better just put everything in the same table. Is there a reason they are splitted?

[Anonymous]

@SpartaBI The relationship is not 1:1, sorry if my response was unclear. The project IDs appear multiple times in both tables and their instances do not correspond to each other so the relationship is m:m. 

Does this change anything? 

thanks again for your help! 

[SpartaBI]

@Anonymous
Ye, this change 🙂
Why do you have duplicate rows for same project in the budget table? Shouldn't each row represent the total budget for that project? I fear that the budget amount is duplicated. Very hard to tell with info supplied
Also, please, anyway, make the relationship one way from the budget to the cost table

[Anonymous]

@SpartaBI the budget table includes each line item making up the total budget for each project. for instance here is a portion of the table:

I apologize for not clearly explaining the budget table in my first post, hopefully this makes a little more sense. 

Essentially I will be taking a sum of the original budget column for each project, and that is the basis of my running remaining budget. 

Thanks again for your help!

[SpartaBI]

@Anonymous no worries 🙂
It should be really easy to solve. The many to many in this case is not a big issue, because your granularity of project is the one you need so the results should be ok. Regarding the line, it looks like you are reducing cost and not RT cost, but the measures I wrote you should have work:

$ Cost = SUM(Cost[Cost Amount])

$ Cost RT = 
VAR _current_date = MAX(Cost[Date])
RETURN 
CALCULATE(
    [$ Cost],
    Cost[Date] <= _current_date
)

Remaining Budget = SUM(Budget[Original Budget]) - [Cost RT]

Are you sure you put [Cost RT] and not just cost in the final measue?

[Anonymous]

@SpartaBI that was it! thank you so much for your help.

I have one last question, instead of the visual displaying each individual cost, is there a way that I could group the costs by month? Since the date column in the cost table is not a date table it won't give me the option to pull only the month and year. 

Thank you so much! 

[SpartaBI]

@Anonymous my pleasure.

Not sure I understood what is missing for you, but I think better anyway for community visability to open a new question in the forum with all the relevant details, your current status and what the issue you need to solve.

I'll be happy to take a look on it, will only get to this tomorrow but I'm pretty sure someone will help.

Don't forget to mark the 1st message I wrote with the codes as a solution for community visabilty.