How to show Late task, Overdue task & ontrack task count in Power BI with Visuals

[amaoa]

Hello All,

I am new to power bi and geting data from Project Online. Could you suggest ways I can show the COUNT of late task, overdue task and ontrack task from the data below? Any visuals that have been used by others that is recommended?If I can get the right expression for all three, I can write an IF statement for the status column. Pls can you verify my expressions below?

Ontrack Task = IF(AND(StartDate>TODAY(),FinishDate>TODAY(),1,0)

Late task = IF(Finish Date<TODAY(),1,0)

Overdue Task = ???

Task Name	Start Date	Finish Date	Actual Start Date	Actual Finish Date	% Comp	Status
Task 1	1/29/21	1/29/21			0
Task 2	6/10/20	6/10/20	6/10/20		5
Task 3	3/8/21	3/12/21			0
Task 4	3/15/21	3/26/21			0
Task 5	1/1/20	1/1/20			0
Task 6	5/28/20	6/24/20	5/28/20		10
Task 7	6/25/20	8/19/20			0
Task 8	1/1/20	1/1/20			0
Task 9	5/28/20	5/28/20	5/28/20		30

[v-xicai · Accepted solution]

Hi @amaoa ,

You may create measure like DAX below.

Overdue Task =
CALCULATE (
    SUMX (
        Table1,
        IF (
            MAX ( [Finish Date] ) < TODAY ()
                && MAX ( [% Comp] ) <> 100
                && ( MAX ( [Finish Date] ) <> BLANK () ),
            1,
            0
        )
    )
)

If I misunderstood it, you may clarify which situation will be marked as "Overdue Task" , "Ontrack Task" and  "Late task" based on your data table. See the similar case: Counts in a period of time.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@amaoa , refer if this can help

https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-gantt-chart/

[az38]

Hi @amaoa 

try X functions like

Ontrack Task Number Measure = 
CALCULATE(SUMX(Table, IF(AND(StartDate>TODAY(),FinishDate>TODAY(),1,0)))

Late task Number Measure = 
CALCULATE(SUMX(Table, IF(Finish Date<TODAY(),1,0)))

[amaoa]

Thanks Super User II. Any suggestions for overdue task?

[amaoa]

Anybody knows how to derive/calculate overdue task? Any help will be greatly appreciated