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