Forum Discussion

Sriku's avatar
Sriku
Helper IV
1 year ago
Solved

Decomposition tree stage value reference

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...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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's avatar
Sriku
Helper IV
1 year ago

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

v-ssriganesh's avatar
v-ssriganesh
Community Support
1 year ago

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's avatar
    Sriku
    Helper IV
    1 year ago

    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's avatar
      v-ssriganesh
      Community Support
      1 year ago

      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's avatar
        Sriku
        Helper IV
        1 year ago

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