Project SChedule Completion %

[Anonymous]

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! 

[collinq · Accepted solution]

@Anonymous ,

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

[collinq]

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]

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]

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. 

[Anonymous]

@collinq,

Still having some trouble. 

When I use a dax for max date, I am getting just one max date which is July 2021. 

Issue is the date Iget is from a software dump and it does not specify start and end date.  A lot of these projects are from the past and some are 2019, 2020, 2021 with budgeted hours plugged in for months that haven't occured yet. 

I created a colum for today's date with Today() and a column for max date. The latter I know I did incorrectly because I am only getting 1 date for all projects. I guess I am missing some steps? 

[collinq]

Hi @Anonymous ,

If you don't have an end date (and a start date) then I don't know how you would determine how far along the schedule is and therefore you would not be able to get to a % Complete.  The MaxDate column should be getting the maximum date from each and every project.  But, it sounds like you don't have that information.  I am assuming that you are trying to get the MaxDate in the PowerQuery column and not in a measure. 

I have done scheduling and schedule reporting for a lot of years - you have to know the duration (or work, or units of people) of the schedule and the last step that has been completed to know the % complete.  It sounds like you don't have that information.  I don't know if you can calculate that with what you have told me that you do have.  I don't think that this is a metric you can provide.  

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]

@collinq,

I do have a start and end date in that they are written in contracts. But when these are reported in the software, its only through current month. I was just about to create a separate matrix table with the project, start date, and end date as columns. This would be a manual table that I'll have to input. This is a bililng analysis I am trying to eventually create. So trying to find available hours of employees for the month/year, show the comparison between billed hours and project schedule, hours by employee, etc. I'll share that shortly. 

[collinq · Accepted solution]

@Anonymous ,

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

[Anonymous]

Hello @collinq ,

Please see below and comment. I think I am getting the correct number now. I am still not getting the right output after using the IF function. Also, the card is only displaying the sum of all the schedule completion %. I understand cards only display scalar but even after I select a project, it only displays the total sum. 

Note: the green background 39% shown is for the hours completed (billed hours/total budgeted hours)

[v-zhenbw-msft · Accepted solution]

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.

[Anonymous]

Hello @v-zhenbw-msft ,

Please see below the screenshot. I have doubled checked the format of date columns and the dax but I am stil lgetting negative numbers in the column. Some are showing up correctly, but others aren't. 

[Anonymous]

@collinq @v-zhenbw-msft ,

I am really close to the solution. The measure I created is not filtered when I select a project. Although, the column I had created for schedule completion works but it's a column and won't show in a card. 

The dax used for both are the same except one gives a column and other gives a measure. I am not sure why the measure isn't reading the project# filter. 

[Anonymous]

Hello @v-zhenbw-msft ,

Yes, I do have a project # column in the Start & End Dates table. Your suggested result has worked. I am running into a small issue. I had Project # as the filter in my slicer from the "Energy Project Hours" Table and the relationship was there between both the PRoject # columns. But now with the new measure, my card only shows the result when I use the PRoject # from Start&End table and not the Energy table. When I use the other PRoject # filter, it deletes everything else and only displays the card.