Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Project SChedule Completion %

Hello,

 

This is my first time posting to the boards, so I hope i've followed the etiquettes. 

 

I am trying to calculate schedule % completion for a list of projects that spans over the years. I tried a simple Today()/Max(date columne) dax but it only returns the net for all the date. Even when I have a filter for projects and select different projects, it doesn't change. I've posted the data. Sorry for the sub-par job on blurring the confidential data. As you can see I have different projects across different years. I do have a Data table created as well. 

 

Thanks for the help! 

 

Need Help
  • collinq's avatar
    collinq
    6 years ago

    Anonymous ,

     

    Yes - a separate table with that information will be able to help you get there!

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    Is the profect # in slicer Select project a column in 'Start & End Dates' table? If not, please check the relationship between your table.

     

    And you can try this calculate column and measure, maybe it is helpful.

     

    Schedule Completion column = 
var monthsgone = 'Start & End Dates'[Today]-'Start & End Dates'[Start Date]
var monthsremaining = 'Start & End Dates'[End Date] - 'Start & End Dates'[Start Date]
var completed = DIVIDE(monthsgone,monthsremaining)
return
IF(completed>=1,1,completed)

     

    Schedule Completion measure = 
var monthsgone = MAX('Start & End Dates'[Today])-MAX('Start & End Dates'[Start Date])
var monthsremaining = MAX('Start & End Dates'[End Date]) - MAX('Start & End Dates'[Start Date])
var completed = DIVIDE(monthsgone,monthsremaining)
return
IF(completed>=1,1,completed)

     

     

     

    If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

    It will be helpful if you can show us the exact expected result based on the tables.

     

    Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

  • collinq's avatar
    collinq
    Super User
    6 years ago

    Hi Anonymous ,

     

    Are you trying to determine the schedule % completion of EACH schedule or of ALL the schedules combined?  Based on your screenshot you are looking for the schedule % complete of each schedule.  To determine schedule % complete, you would need to know the total duration of the schedule and how much of the duration is completed.  Then, the calculation would be duration completed divided by total duration.

     

    I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello collinq ,

       

      I am trying to put a card on my dashboard, so when a project a selected, it would show me the completion % of that project. I assumed when the project is selected from the slicer, it would automatically assume the start and finish when the filter is applied and then provide a % based on Today(). But right now I am getting just one number which is completion % for all the projects combined. 

      • collinq's avatar
        collinq
        Super User
        6 years ago

        Hi Anonymous ,

         

        So, I am not quite sure that is the correct definition of "% Complete".

         

        Its sounds like you are actually trying to determine today's date from the max date of each project.  To do that, I would add two columns - one for today.  Which, in Power BI columns is actually 

        DateTime.LocalNow()

        Then, you can get the maxdate from each project.

        Then, you can subtract the now field from the maxdate.