Forum Discussion
Budget Remaining Multiple Projects in Dataset
I have two tables (expenses and budgeted) each with data for multiple projects in them. I am trying to use Power BI to create a report that 1) includes a line chart with the budget remaining where each project has it's own line and 2) the user could select a slicer to focus on just one project at a time. I have read multiple posts and suggestions about using calculate, sum, and filtering by date less than. The solutions that I've followed have gotten me part way there but they end up with the starting amount being the same for all them (i.e. the total combined funding for all projects) however I need each line should only have the funding amount for that specific project.
Below are samples of the tables and an example visual of what I'm trying to achieve. I'm just learning Power BI so any examples that you can provide on how to achieve this would be appreciated.
Sample of budgeted table, funding may come in at multiple times during the project
| ProjectID | Amount | FundingDate |
| A | 400.00 | 12/1/2022 |
| A | 300.00 | 2/1/2023 |
| B | 1,500.00 | 12/1/2022 |
| C | 1,500.00 | 12/1/2022 |
| C | 1,500.00 | 3/15/2023 |
Sample of expenses table
| ProjectID | ExpenseDate | Amount | Category | Description |
| A | 1/1/2023 | 100.00 | CAT-1 | some |
| A | 1/20/2023 | 100.00 | CAT-2 | thing |
| A | 2/5/2023 | 100.00 | CAT-1 | we |
| A | 2/20/2023 | 100.00 | CAT-2 | did |
| A | 3/1/2023 | 100.00 | CAT-1 | cost |
| A | 3/25/2023 | 100.00 | CAT-2 | the |
| A | 4/2/2023 | 100.00 | CAT-1 | project |
| A | 4/21/2023 | 100.00 | CAT-2 | money |
| B | 1/5/2023 | 200.00 | CAT-1 | some |
| B | 1/20/2023 | 200.00 | CAT-1 | thing |
| B | 2/20/2023 | 400.00 | CAT-1 | we |
| B | 3/15/2023 | 200.00 | CAT-1 | did |
| B | 3/28/2023 | 400.00 | CAT-2 | cost |
| C | 1/1/2023 | 500.00 | CAT-1 | the |
| C | 2/1/2023 | 750.00 | CAT-1 | project |
| C | 3/1/2023 | 750.00 | CAT-1 | money |
| C | 4/1/2023 | 1,000.00 | CAT-1 | money |
Sample line chart from Excel, manually created for illustration purpose
2 Replies
shepa006 Not sure if it is clear how you want to visualize it.
For example, project A has expenses over the period, and is shown on the line visual, assume $400 is the remaining budget for that project, how would you like to see it on the line visual because there is no line for it, it is just a remaining amount.
parry2k I hadn't thought about showing the project ending amount. I was just focused on learning how to do the running amount remaining. But you bring up a good point, my thoughts on this are below but I'm open to suggestions if you've done this before and have a way that worked well for you:
Could I add another table with the project start and end dates, this would be different than the funding approved that I have in my original post, and add a point marker like a diamond for each project that shows the start and end date? Again using project A as an example it would have a Dec point of $400 for the project start date and a point in April of $-100 for the end. That might get really distracting though to the viewer but it could help address the issue you bring up of having a remaining amount just floating out there after the project end date. This could also help address the reality that not all projects are going to start and end on the same dates.This is just one piece that I'm trying to learn. Next I will be digging into learning how to create a date table so that I can set custom hierarchy to allow the user to view the report based on calendar year or fiscal year.
