Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

How Do I Calculate Project Activity Back and Forth

‎04-06-2022 09:04 AM

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
Labels:
Message 1 of 8
970 Views
0
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support
In response to Anonymous

‎04-12-2022 12:32 AM

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

 

vchenwuzmsft_0-1649748785572.png

 

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.

 

View solution in original post

How Do I Calculate Project Activity Back and Forth.pbix
Message 4 of 8
888 Views
0
7 REPLIES 7
v-chenwuz-msft
Community Support
Community Support

‎04-10-2022 11:40 PM

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

Message 2 of 8
903 Views
0
Anonymous
Not applicable
In response to v-chenwuz-msft

‎04-11-2022 05:17 AM

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.

Message 3 of 8
898 Views
0
v-chenwuz-msft
Community Support
Community Support
In response to Anonymous

‎04-12-2022 12:32 AM

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

 

vchenwuzmsft_0-1649748785572.png

 

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.

 

How Do I Calculate Project Activity Back and Forth.pbix
Message 4 of 8
889 Views
0
Anonymous
Not applicable
In response to v-chenwuz-msft

‎04-22-2022 09:12 AM

Could you help me with what you mean by create an "Index Column"? Thank you 

Message 6 of 8
873 Views
0
v-chenwuz-msft
Community Support
Community Support
In response to Anonymous

‎04-24-2022 06:33 PM

Hi @Anonymous ,

 

Create an index in power query.

vchenwuzmsft_0-1650850375511.png

 

Add an index column - Power Query | Microsoft Docs

Message 7 of 8
865 Views
0
Anonymous
Not applicable
In response to v-chenwuz-msft

‎04-26-2022 07:04 AM

Thank you for this. I was able to group and index all of the projects. I reposted my question with a new issue that is coming up in "how many times" column. It is under the question

"Count Column by Group and filter" 

 

https://community.powerbi.com/t5/Desktop/Count-Column-by-Group-and-filter/m-p/2473373#M881651

 

Maybe you could take a look at this new issue. I think it is almost solved! Thank you again!

Message 8 of 8
858 Views
0
Anonymous
Not applicable
In response to v-chenwuz-msft

‎04-12-2022 06:00 AM

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

Message 5 of 8
887 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All