Calculating TIme Spent for each status / stage

[rkee10]

I would like to know on how to calculate the spent time for each stage, see below sample table with created date: 6/2/2024:

Date	From Status	To Status
06/06/2024	Stage 1	Stage 2
6/23/2024	Stage2	Stage 3
07/06/2024	Stage 3	Stage 2
8/23/2024	Stage 2	Stage 3
9/22/2024	Stage 3	Stage 4

The way it should be calculated:

Stage 1 =  (Stage 1 to Stage 2) - Created Date

Stage 2 = MAX(Stage 2 to Stage 3) - MIN(Stage 1 to Stage 2) 

The reason of why this is being calculated is because start of Stage 2 is when it was transferred from Stage 1 and the end of Stage 2 when it transferred to stage 3.  But if you'll notice, the item went back to Stage 2 and then Stage 3, we just need to get the MIN of the (Stage 1 to Stage 2) and MAX of the (Stage 2 to Stage 3) to get the total time spent in Stage 2.

Then we go back for Stage 3 = (Stage 3 to Stage 4) - MAX(Stage 2 to Stage 3).

For Stage 4, it is just equal to (Stage 3 to Stage 4) - Created.

Also, please take note that the "going back and forth" of stages would happen in any stage, so Stage 2 might go back to Stage 1 or even the stage 4 might go back to stage 3 but it will not skip a level when going back to previous stage.  Like, Stage 3 won't go back to Stage 1 nor Stage 4 going back to Stage 2 or Stage 1.

Lastly, CANCELLATION / REJECTION, can happen anytime, so that is adding some complexity in the DAX calculation.  Of course, if we have this status (REJECTION/CANCELLATION) then it will stop there.  For eample, this status appears on Stage 2 to Stage 3, then we are done, and w won't be having any Stage 3 to Stage 4.

I hope this clear, thanks for the usual support.

[v-achippa]

Hi @rkee10,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[bhanu_gautam]

@rkee10 First, create calculated columns to capture the minimum and maximum dates for each stage transition.

MinStage1ToStage2 = CALCULATE(MIN('Table'[Date]), 'Table'[From Status] = "Stage 1" && 'Table'[To Status] = "Stage 2")
MaxStage2ToStage3 = CALCULATE(MAX('Table'[Date]), 'Table'[From Status] = "Stage 2" && 'Table'[To Status] = "Stage 3")
MinStage2ToStage3 = CALCULATE(MIN('Table'[Date]), 'Table'[From Status] = "Stage 2" && 'Table'[To Status] = "Stage 3")
MaxStage3ToStage4 = CALCULATE(MAX('Table'[Date]), 'Table'[From Status] = "Stage 3" && 'Table'[To Status] = "Stage 4")

Next, create measures to calculate the time spent in each stage.

TimeSpentStage1 = DATEDIFF(MIN('Table'[Created Date]), [MinStage1ToStage2], DAY)

TimeSpentStage2 = DATEDIFF([MinStage1ToStage2], [MaxStage2ToStage3], DAY)

TimeSpentStage3 = DATEDIFF([MaxStage2ToStage3], [MaxStage3ToStage4], DAY)

TimeSpentStage4 = DATEDIFF(MIN('Table'[Created Date]), [MaxStage3ToStage4], DAY)

[rkee10 · Accepted solution]

Thanks, @bhanu_gautam  - will review your suggestions and get back to you. 🙂

[v-achippa]

Hi @rkee10,

As we haven’t heard back from you, we wanted to kindly follow up to check if you had a chance to try the solution. Has your issue been resolved, or do you need any further assistance?

If the super user's response helped in resolving the issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @rkee10,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved? or do you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa