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 ...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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