Forum Discussion

barry2099

Copper Contributor

Feb 23, 2023

Solved

Task Status

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

Paul_Mather
Feb 24, 2023
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_Mather

MVP

Feb 24, 2023

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

barry2099
Copper Contributor
Feb 24, 2023
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 !
barry2099
Copper Contributor
Feb 24, 2023
Paul,
Thanks so much. It did help, plus it gave me enough clues to go dig out the formula for myself.

Thanks Again
Barry