Forum Discussion
Project SChedule Completion %
Anonymous ,
Yes - a separate table with that information will be able to help you get there!
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.
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?
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!
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.
Anonymous ,
Yes - a separate table with that information will be able to help you get there!
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)