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