cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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 NameStart DateFinish DateActual Start DateActual Finish Date% CompStatus
Task 11/29/211/29/21  0 
Task 26/10/206/10/206/10/20 5 
Task 33/8/213/12/21  0 
Task 43/15/213/26/21  0 
Task 51/1/201/1/20  0 
Task 65/28/206/24/205/28/20 10 
Task 76/25/208/19/20  0 
Task 81/1/201/1/20  0 
Task 95/28/205/28/205/28/20 30 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

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

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Super User IV
Super User IV

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

@amaoa , refer if this can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

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

Thanks Super User II. Any suggestions for overdue task?

Highlighted
Frequent Visitor

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

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

Highlighted
Community Support
Community Support

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

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.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors