Decomposition tree stage value reference

[Sriku]

Hi, 

I want to pick the earlier stage and calculate sum of "Days in Ticket group", on selection filter on "Update ticket group - Start",

and sort "Update - Timestamp" from , I want to pick the daya taken to move one another

Eg:, If I "Update ticket group" Starting Stage is COE_T and next stage is "All_My" so I want to pick 0.77 days taken, similary, If ticket move to another stage "All_My" to next stage is "DE" time taken is 0.08 days, and so on.

Please share knowlege to achieve this

Ticket ID	Update ticket group	Update - Timestamp	Update ticket group - Start	Update ticket group - Next	Update ticket group - Prev	Days in Ticket group	Ticket Indexing
250158	COE_T	2025-02-26 15:31:34	COE_T	All_My		0.77	1
250158	All_My	2025-02-27 10:03:53	COE_T	DE	COE_T	0.08	2
250158	DE	2025-02-27 11:53:41	COE_T	All_My	All_My	0.89	3
250158	All_My	2025-02-28 09:16:58	COE_T	DE	DE	0.02	4
250158	DE	2025-02-28 09:44:46	COE_T	COE_T	All_My	0.01	5
250158	COE_T	2025-02-28 09:58:26	COE_T	All_My	DE	4.01	6
250158	All_My	2025-03-04 10:09:44	COE_T	DE	COE_T	0.06	7
250158	DE	2025-03-04 11:31:55	COE_T	All_My	All_My	0.08	8

[v-ssriganesh]

Hello @Sriku,
Thank you for reaching out to the Microsoft Fabric Community forum.

I’ve reproduced your scenario successfully using sample data, and the logic works as expected in Power BI Desktop. Please find the .pbix file attached for your reference. I’ve also used the following calculated columns:

• Get Previous Stage

Previous Stage =

VAR CurrentIndex = 'Tickets'[Ticket Indexing]

VAR CurrentTicket = 'Tickets'[Ticket ID]

RETURN

    CALCULATE(

        MAX('Tickets'[Update ticket group]),

        FILTER(

            'Tickets',

            'Tickets'[Ticket ID] = CurrentTicket &&

            'Tickets'[Ticket Indexing] = CurrentIndex - 1

        )

    )

• Stage Transition

Stage Transition =

'Tickets'[Previous Stage] & " → " & 'Tickets'[Update ticket group]

The Decomposition Tree and Table visual both reflect the correct stage transitions and time durations.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Sriku]

Thanks for your prompt response. But this is not I looking for

[v-ssriganesh]

Hi @Sriku,

Thanks for the clarification, I appreciate you getting back.

To better assist you, could you please help me understand your exact requirement? Based on your original post, I had assumed you were looking to calculate the time spent in each stage of a ticket and visualize it using the transition (e.g: Stage A → Stage B). However, it seems there’s a different outcome you're aiming for.

Could you please let me know:

• What is the expected output you would like to see?

• Are you looking to measure time between specific stages, or something else like tracking duration per ticket or per status?

• Do you have a sample output format or logic in mind?
  
  

Once I have this, I’ll rework the solution and share an updated .pbix file accordingly.

Looking forward to your response.

Thank you.

[Sriku]

@v-ssriganesh , I am looking to measure time between start stage to next stage of a particular Ticket, i.e how much time it takes to move from one stage to another

[v-ssriganesh · Accepted solution]

Hi @Sriku,
Thank you for the clarification.

Based on your updated requirement, calculating the time it takes for a ticket to move from one stage to the next. I’ve revised the approach accordingly.

I’ve added two new calculated columns:

• Previous Update Timestamp

This finds the timestamp of the previous stage within the same ticket.

Previous Update Timestamp =

VAR CurrentIndex = 'Tickets'[Ticket Indexing]

VAR CurrentTicket = 'Tickets'[Ticket ID]

RETURN

    CALCULATE(

        MAX('Tickets'[Update - Timestamp]),

        FILTER(

            'Tickets',

            'Tickets'[Ticket ID] = CurrentTicket &&

            'Tickets'[Ticket Indexing] = CurrentIndex - 1

        )

    )

• Time Between Stages (Hours)

This calculates the duration between the current and previous stage transitions.

Time Between Stages (Hours) =

VAR PrevTime = 'Tickets'[Previous Update Timestamp]

VAR CurrTime = 'Tickets'[Update - Timestamp]

RETURN

    IF(

        NOT ISBLANK(PrevTime),

        DATEDIFF(PrevTime, CurrTime, HOUR)

    )

I’ve also attached an updated .pbix file for your reference. Please feel free to test it.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[Sriku]

@v-ssriganesh , Thanks for your response. Let me try it out 

[mh2587]

Sum Days from Selected Start Stage = 
VAR SelectedStartStage = SELECTEDVALUE('Tickets'[Update ticket group - Start])
RETURN
SUMX(
    FILTER(
        'Tickets',
        'Tickets'[Update ticket group - Start] = SelectedStartStage &&
        'Tickets'[Update ticket group - Prev] = SelectedStartStage
    ),
    'Tickets'[Days in Ticket group]
)
//Try this one