Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How Do I Calculate Project Activity Back and Forth

I am trying to calculate the time duration a project stays in any given stage. The problem is, that a project can jump back and forth between stages and departments depending of the scope of the project.

 

How would I calculate total time in each Stage AND how many times does a project enter/leave a stage? I will need to know the duration of time within each Stage instance.

 

Any help would be greatly appreciated!

 

Below is a table table of 1 project. Currently, all projects are housed in one large status table.  Everytime an action occurs, a new Status_ID is created and the progress is added.

 

Status_IdProjectIdStageIdCreatedNoteParent Status_IdStage_Name
3814110312/10/20222.10.22 Saved 3811Tech Review
3835110312/10/20222.10.22. Saved 3829Tech Review
3829110312/10/20222.10.22  update released drawings.3814Tech Review
3811110312/10/20222.10.22 update released drawings3727Tech Review
3837110472/10/20222.10.22  Please release to production3835Production
3727110312/9/20222.10.22  update released drawings3688Tech Review
3688110312/8/20222.8.22  update released drawings3675Tech Review
3675110312/8/20222.8.22. Proofsent back3672Tech Review
3672110312/8/20222.8.22 updated proof3540Tech Review
3703110472/8/20222.8.22: Please release to production0Production
3540110312/4/20222.4.22. Saved3484Tech Review
3482110312/3/20222.3.22 Saved3481Tech Review
3484110312/3/20222.3.22 saved 3482Tech Review
3481110232/3/20222.3.22 2nd proof3396Review Proof
3396110312/2/20222.2.22 Sent proof back 3391Tech Review
3391110312/2/20222.2.22 update 2nd proof3227Tech Review
3222110221/28/20221.28.22: Please see changes 0Graphics
3227110311/28/20221.27.22 update 1st proof3222Tech Review
3151110311/27/20221.27.22 update 1st proof3128Tech Review
3177110311/27/20221.27.22 update 1st proof3151Tech Review
3128110311/27/20221.27.22 update 1st proof2871Tech Review
3187110871/27/20221.27.22 1st proof3184Review Proof
3184110311/27/20221.27.22 Internal Proofing3177Tech Review
2871110311/21/20221.26.22 internal proofing2294Tech Review
2294110211/7/20221.20.22: Please note0Graphics
8791112812/1/2021 0Follow up Estimators
7731112411/29/2021Please revise the quote 0Estimating
2781112411/10/2021compile a quote0Estimating
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Reaching this is more difficult. You can try the following steps.
    First create an index column.
    Create a calculated column to calculate the number of times the current row belongs to tech review.

     

    How many times =
VAR _maxIndexOf_ProjectAndStage =
    MAXX (
        FILTER (
            'Table',
            'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && [Stage_Name] = "Tech Review"
        ),
        [Index]
    )
VAR _maxIndexOfProject =
    MAXX (
        FILTER ( 'Table', 'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) ),
        [Index]
    )
VAR _countNotTechReview =
    COUNTROWS (
        FILTER (
            'Table',
            [Index] > EARLIER ( 'Table'[Index] )
                && [Stage_Name] <> "Tech Review"
        )
    )
RETURN
    IF (
        [Stage_Name] = "Tech Review",
        _countNotTechReview - ( _maxIndexOfProject - _maxIndexOf_ProjectAndStage - 1 )
    )

     


    Create another column to calculate how many days the current tech review has consumed.

     

    length of XXX calendar days =
VAR _start =
    MAXX (
        FILTER (
            'Table',
            [ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && [How many times] = EARLIER ( 'Table'[How many times] )
        ),
        [Created]
    )
VAR _end =
    MINX (
        FILTER (
            'Table',
            [ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && [How many times] = EARLIER ( 'Table'[How many times] )
        ),
        [Created]
    )
RETURN
    IF ( [Stage_Name] = "Tech Review", VALUE ( _start - _end ) + 1 )

     

    Finally, in the visual chart, seperate the maximum number of times for the current project id and average the number of days using the following formula.

     

    average length of XXX calendar days = 
AVERAGEX(VALUES('Table'[length of XXX calendar days]),[length of XXX calendar days])

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I need to know how many times a project has to have the same step repeated in the production process.

       

      I am trying to get to the level that says:

      Tech review had to be started 6 seperate times. Each instance had an average length of XXX calendar days.

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        Reaching this is more difficult. You can try the following steps.
        First create an index column.
        Create a calculated column to calculate the number of times the current row belongs to tech review.

         

        How many times =
VAR _maxIndexOf_ProjectAndStage =
    MAXX (
        FILTER (
            'Table',
            'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && [Stage_Name] = "Tech Review"
        ),
        [Index]
    )
VAR _maxIndexOfProject =
    MAXX (
        FILTER ( 'Table', 'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) ),
        [Index]
    )
VAR _countNotTechReview =
    COUNTROWS (
        FILTER (
            'Table',
            [Index] > EARLIER ( 'Table'[Index] )
                && [Stage_Name] <> "Tech Review"
        )
    )
RETURN
    IF (
        [Stage_Name] = "Tech Review",
        _countNotTechReview - ( _maxIndexOfProject - _maxIndexOf_ProjectAndStage - 1 )
    )

         


        Create another column to calculate how many days the current tech review has consumed.

         

        length of XXX calendar days =
VAR _start =
    MAXX (
        FILTER (
            'Table',
            [ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && [How many times] = EARLIER ( 'Table'[How many times] )
        ),
        [Created]
    )
VAR _end =
    MINX (
        FILTER (
            'Table',
            [ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && [How many times] = EARLIER ( 'Table'[How many times] )
        ),
        [Created]
    )
RETURN
    IF ( [Stage_Name] = "Tech Review", VALUE ( _start - _end ) + 1 )

         

        Finally, in the visual chart, seperate the maximum number of times for the current project id and average the number of days using the following formula.

         

        average length of XXX calendar days = 
AVERAGEX(VALUES('Table'[length of XXX calendar days]),[length of XXX calendar days])

         

         

        Pbix in the end you can refer.

        Best Regards

        Community Support Team _ chenwu zhu

         

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