SOLVED

Task Status

Copper Contributor

Hello All,

 

I'm using P4W with the MS Project for the Web Power BI Template to create reports with Power BI.  My question is how does P4W calculate if a Task is Late or Overdue ?

 

Thanks in Advance

3 Replies
best response confirmed by Dale Howard (MVP)
Solution

Hello @barry2099 ,

This is how it is calculated in the Power BI template:

  • Late Tasks = CALCULATE(COUNTA([Task ID]),FILTER('Project Tasks',[Task KPI]=3))
  • Overdue Tasks = CALCULATE(COUNTA([Task ID]),FILTER('Project Tasks',[Task KPI]=4))
  • Task KPI = if [msdyn_progress] = 1 then 0 else if [msdyn_scheduledstart] > DateTime.LocalNow() then 1 else if [msdyn_scheduledstart] < DateTime.LocalNow() and [msdyn_scheduledend] >= DateTime.LocalNow() and [msdyn_progress] > 0 then 2 else if [msdyn_scheduledstart] < DateTime.LocalNow() and [msdyn_scheduledend] >= DateTime.LocalNow() and [msdyn_progress] = 0 then 3 else if [msdyn_scheduledend] < DateTime.LocalNow() and [msdyn_progress] < 1 then 4 else 1

Hope that helps

Paul

 

Paul,
Thanks so much. It did help, plus it gave me enough clues to go dig out the formula for myself.

Thanks Again
Barry
Paul, a follow up question on the template if you don't mind.

My concern is with the Task Overview page of the report. There is a problem with the filtering on this page in the template, which I found and corrected. With my dataset the "Task" card visualization displays 412, however the table at the bottom of the page only displays 23 rows. Every visualization has the same filter...which is none. This delta does not make sense to me and I can't find a reason for it. What am I missing ?

Thanks again !
1 best response

Accepted Solutions
best response confirmed by Dale Howard (MVP)
Solution

Hello @barry2099 ,

This is how it is calculated in the Power BI template:

  • Late Tasks = CALCULATE(COUNTA([Task ID]),FILTER('Project Tasks',[Task KPI]=3))
  • Overdue Tasks = CALCULATE(COUNTA([Task ID]),FILTER('Project Tasks',[Task KPI]=4))
  • Task KPI = if [msdyn_progress] = 1 then 0 else if [msdyn_scheduledstart] > DateTime.LocalNow() then 1 else if [msdyn_scheduledstart] < DateTime.LocalNow() and [msdyn_scheduledend] >= DateTime.LocalNow() and [msdyn_progress] > 0 then 2 else if [msdyn_scheduledstart] < DateTime.LocalNow() and [msdyn_scheduledend] >= DateTime.LocalNow() and [msdyn_progress] = 0 then 3 else if [msdyn_scheduledend] < DateTime.LocalNow() and [msdyn_progress] < 1 then 4 else 1

Hope that helps

Paul

 

View solution in original post