Calculating Total Project Backlog Per Month

[Leggott]

Hello All, new user here looking for some help.

I have a total list of my teams projects, their estimated end date, total fee and Current Spent amount. I am looking to calculate and visualize my teams total project cost remaining per month. Projects are further broken down by tasks so being able to filter out by task is also required.

Project Backlog = Total Fee - Current Spend

Project Monthly Backlog = Backlog / months remaining

Total project monthly backlog = sum of all project monthly backlogs in the months the project is 'active' (up to estimated end date)

Here is a small sample of representative data.

Project Number	Task Number	Estimated completion	Total Fee	Current Spend
10001.001	01-000	December 15, 2020	1000.00	800.00
10001.001	03-000	December 15, 2020	2000.00	1000.00
10001.001	04-000	December 15, 2020	4500.00	3000.00
10002.001	02-000	September 15, 2020	4000.00	2000.00
10002.001	03-000	September 15, 2020	7000.00	1000.00
10002.002	04-000	October 5, 2020	3000.00	2000.00
10002.003	03-000	December 1, 2020	2000.00	1000.00

Thanks!

[Greg_Deckler]

@Leggott So what is the expected output from the sample data? Seems like the first one should just be:

Measure = SUM([Total Fee]) - SUM([Current Spend])

[Leggott]

@Greg_Deckler Thanks for the reply.

I am looking to summarize the backlog per month for any project that is expected to be worked on that month.

As an example lets look at three projects, as a reminder my inputs are total project Cost, project spend, and end date:

A with task 1 has 5,000.00 of work over 5 months.

A with task 2 has 10,000.00 of work over 5 months.

B with task 2 has 3,000.00 of work over 3 months.

C with task 1 has 4,000.00 of work over 4 months.

My output would be a graph that shows 5,000.00 of backlog in months 1, 2, 3 from all projects and tasks, 4,000.00 of backlog in month 4 from projects A and B, and 3,000.00 of backlog for month 5 from project A.

Being able to filter this graph by task codes would be my next step. so filter for task 1 backlog only over those 5 months.

Hope this clarifies things.

[v-lili6-msft · Accepted solution]

HI  @Leggott 

For your case, you need to add a start date column in your table, like this:

Then try this way as below:

Step1:

Create a dim Calendar table

Eg.

Calendar = CALENDARAUTO()

Step2:

create a monthly project Cost column

monthly project Cost = 'Table'[total project Cost] / (DATEDIFF('Table'[Start Date],'Table'[end date],MONTH)+1)

Step3:

Create a measure as below:

Total = 
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Table',"MonthYearBegin",VALUE(YEAR([Start Date]) & FORMAT(MONTH([Start Date]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([end date]) & FORMAT(MONTH([end date]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Project",[Project Number ],
    "Year",[Year],
    "Month",[Month],
    "Project Cost",[monthly project Cost]
)
RETURN SUMX(tmpTable,[Project Cost])

Result:

here is a same blog, you could refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

and here is my sample pbix file, please try it.

Regards,

lin

[MoMrCrane]

When you return 

SUMX(tmpTable,[Project Cost]), what is [Project Cost]? It's not anywhere else in the file.

[Leggott]

@v-lili6-msft Thank you so much for the reply I will give this a try today!