How Do I Calculate Project Activity Back and Forth

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.