Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating Total Project Backlog Per Month

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 NumberTask NumberEstimated completionTotal FeeCurrent Spend

10001.001

01-000December 15, 20201000.00800.00
10001.00103-000December 15, 20202000.001000.00
10001.00104-000December 15, 20204500.003000.00
10002.00102-000September 15, 20204000.002000.00
10002.00103-000September 15, 20207000.001000.00
10002.00204-000October 5, 20203000.002000.00
10002.00303-000December 1, 20202000.00 1000.00

 

Thanks!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  Anonymous 

    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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous 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])

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lili6-msft
        Community Support
        6 years ago

        HI  Anonymous 

        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