Progress Tracker

[LuisGerardoSR]

Hi,

I would like to use a visual that would let me track the progress of the activities by area and be able to see the following criteria:

• If the activity started on the date suggested
• How much progress has been made after it started
• If the activity is over due or not
• If the whole project is over due

this is the table that shows the tasks assigned to each area:

Area	Task	Estimated Days	Begin Date
Logistics	Ship Parts	7	8/27/2020
Maintenance	Repair Engines	21	9/3/2020
Operations	Drill Well	56	9/24/2020

The due date for the whole project is 9/24/2020.

Thanks!

[harshnathani · Accepted solution]

Hi @LuisGerardoSR ,

Create something like this.

Calculated Column

ESTIMATED START DATE1 =
VAR prevdate =
    CALCULATE (
        MAX ( 'Table'[Begin Date] ),
        FILTER (
            'Table',
            'Table'[Begin Date]
                < EARLIER ( 'Table'[Begin Date] )
        )
    )
VAR est =
    CALCULATE (
        MAX ( 'Table'[Estimated Days] ),
        FILTER (
            'Table',
            'Table'[Begin Date] = prevdate
        )
    )
RETURN
    est + prevdate

Measures

Did it start on Estimate Date =
VAR _datediff =
    DATEDIFF (
        MAX ( 'Table'[ESTIMATED START DATE1] ),
        MAX ( 'Table'[Begin Date] ),
        DAY
    )
RETURN
    IF (
        _datediff = 0,
        "Yes",
        "No"
    )

Overdue =
VAR _datediffactivitybeginend =
    DATEDIFF (
        MAX ( 'Table'[Begin Date] ),
        MAX ( 'Table'[End Date] ),
        DAY
    )
RETURN
    IF (
        _datediffactivitybeginend
            > MAX ( 'Table'[Estimated Days] ),
        "Overdue",
        "On-Time"
    )

Project Overdue = 
var _beginactivity = CALCULATE(MIN('Table'[Begin Date]),ALL('Table'))---Date of 1st Task of project
var _endactivity = CALCULATE(MAX('Table'[End Date]),ALL('Table'))

var _datediffactivitybeginend = CALCULATE( DATEDIFF(_beginactivity,_endactivity,DAY))

RETURN

IF(_datediffactivitybeginend <= SUM('Table'[Estimated Days]), "On-Time","OverDue")

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[v-lionel-msft · Accepted solution]

Hi @LuisGerardoSR ,

Maybe you can do like this.

IF suggested date = 
IF(
    MAX(Sheet5[Begin Date]) = DATE(2020, 8, 27),
    "Yes", "No"
)

How much progress = 
CALCULATE(
    COUNT(Sheet5[Task]),
    ALLEXCEPT( Sheet5, Sheet5[Task])
)

If the activity is over = 
VAR x = 
DATEDIFF(
    MAX(Sheet5[Begin Date]),
    MAX(Sheet5[End Date]),
    DAY
)
RETURN
IF(
    x > MAX(Sheet5[Estimated Days]),
    "Yes", "No"
)

If the whole project is over due = 
VAR x = 
DATEDIFF(
    MAX(Sheet5[Begin Date]),
    MAX(Sheet5[End Date]),
    DAY
)
RETURN
IF(
    x > 84,
    "Yes", "No"
)

But to be honest, your data lacks completeness. If this is not what you want, please provide a more complete data table, including more key columns and duplicate rows.

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @LuisGerardoSR ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[harshnathani]

Hi @LuisGerardoSR ,

Thanks for posting, but can you share logic for each calculation.

Regards,

HN

[LuisGerardoSR]

Hello,

Right now I do not have any measures or anything else built for this report.

What I am looking for is for measures and visuals that would help me show that the tasks assigned to Maintenance have a limit of days to be completed and are necessary for Operations to begin with their tasks assigned. Also, for logistics to begin working on their tasks, Maintenance and Operations should all be done with everything assigned to them.

[harshnathani]

Hi @LuisGerardoSR ,

I meant this

• If the activity started on the date suggested   --> What is the Suggested Date?
• How much progress has been made after it started  --> HOw do you track progress??
• If the activity is over due or not  --> What is the logic for overdue date?  How to check if it is overdue? 
• If the whole project is over due  

Share some more details and also the expected output you would want to see.

Regards,

HN

[LuisGerardoSR]

the answers in red:

• If the activity started on the date suggested   --> What is the Suggested Date? for Logistics the suggested to begin is 8/27/2020.

• How much progress has been made after it started  --> HOw do you track progress?? progress can be track by showing how many have past after the actual begin date.

• If the activity is over due or not  --> What is the logic for overdue date?  How to check if it is overdue? if it is overdue is because it took more days than planned.

• If the whole project is over due --- the project is over due if the whole tasks took more than 84 days (the estimated days for all area to complete their tasks).

[harshnathani]

HI @LuisGerardoSR ,

You will need a end date for the tasks. That is not available.

How do I detemine how many days did the task Repair Engine take if I do not have the End Date?

I may be missing something. But pls share all information to help you.

Regards,

HN

[LuisGerardoSR]

you are right...

I added another column with the end date.

Area	Task	Estimated Days	Begin Date	End Date
Logistics	Ship Parts	7	8/27/2020	9/3/2020
Maintenance	Repair Engines	21	9/3/2020	9/24/2020
Operations	Drill Well	56	9/24/2020	11/19/2020

[harshnathani · Accepted solution]

Hi @LuisGerardoSR ,

Create something like this.

Calculated Column

ESTIMATED START DATE1 =
VAR prevdate =
    CALCULATE (
        MAX ( 'Table'[Begin Date] ),
        FILTER (
            'Table',
            'Table'[Begin Date]
                < EARLIER ( 'Table'[Begin Date] )
        )
    )
VAR est =
    CALCULATE (
        MAX ( 'Table'[Estimated Days] ),
        FILTER (
            'Table',
            'Table'[Begin Date] = prevdate
        )
    )
RETURN
    est + prevdate

Measures

Did it start on Estimate Date =
VAR _datediff =
    DATEDIFF (
        MAX ( 'Table'[ESTIMATED START DATE1] ),
        MAX ( 'Table'[Begin Date] ),
        DAY
    )
RETURN
    IF (
        _datediff = 0,
        "Yes",
        "No"
    )

Overdue =
VAR _datediffactivitybeginend =
    DATEDIFF (
        MAX ( 'Table'[Begin Date] ),
        MAX ( 'Table'[End Date] ),
        DAY
    )
RETURN
    IF (
        _datediffactivitybeginend
            > MAX ( 'Table'[Estimated Days] ),
        "Overdue",
        "On-Time"
    )

Project Overdue = 
var _beginactivity = CALCULATE(MIN('Table'[Begin Date]),ALL('Table'))---Date of 1st Task of project
var _endactivity = CALCULATE(MAX('Table'[End Date]),ALL('Table'))

var _datediffactivitybeginend = CALCULATE( DATEDIFF(_beginactivity,_endactivity,DAY))

RETURN

IF(_datediffactivitybeginend <= SUM('Table'[Estimated Days]), "On-Time","OverDue")

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!