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

     

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

Hi  Anonymous , 

 

The duration can be referred to this formula.

 

Duration = value(MAX('Table'[Created])-MIN('Table'[Created]))+1

 


Not sure what your standard is for calculating entry and leave procedures. Can you give some idea of what the desired outcome would look like?

 How to Get Your Question Answered Quickly - Microsoft Power BI Community


Best Regards

Community Support Team _ chenwu zhu

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        This is Great! How woudl I change the index columns so that its not specific to "Tech Review". I want to make sure I am capturing the all of the different Stages