How can I calculate the sum of the time between many updates from the same ticket ID.

[FelippeAzevedo7]

Hello All

I would like to ask for support because I need to know the sum of the time between each update of the same ticket.

And in the end, for ticket XXXXXX  we had a total of hh:mm.

I will provide just 2 samples of tickets, but I have many more.

Many tks for the supporting.

[FelippeAzevedo7]

Hello Greg

How are you?

Let me explain once more, because I don't think I've made it clear.
I need to know the total time between dates for the same ticket.

Thanks for your help.

[MarkLaf · Accepted solution]

This can be done with a DATEDIFF measure:

Ticket Duration (Days) = 
DATEDIFF( 
    MIN( 'Table'[Comment date (UTC)] ), 
    MAX( 'Table'[Comment date (UTC)] ), 
    HOUR 
) / 24

Output in a table visual (with Totals off):

Edit:

In case you need duration in d:hh:mm:ss format rather than as a decimal in days:

Ticket Duration (d:hh:mm:ss) = 
VAR _secsDifTotal = 
DATEDIFF( 
    MIN( 'Table'[Comment date (UTC)] ), 
    MAX( 'Table'[Comment date (UTC)] ), 
    SECOND 
)
VAR _secsPerDay = 24 * 60 * 60
VAR _dayPart = INT( _secsDifTotal / _secsPerDay )
VAR _timePart = MOD( _secsDifTotal, _secsPerDay ) / _secsPerDay
RETURN
_dayPart & ":" & FORMAT( _timePart, "hh:mm:ss" )

[FelippeAzevedo7]

Hello

In this case, does it only calculate the difference between the last and first dates, or does it add up the total time between each date to the same ticket?

Tks

[MarkLaf]

It's just getting the difference. If you want to do something like not count the duration for some entries, then it probably makes sense to go the route others suggested re: adding a calculated column to your table that provides the duration using previous ticket row.

[techies]

Hi @FelippeAzevedo7 please check this

calculated column--

Previous_Comment_Date =

VAR PrevDate =

    CALCULATE(

        MAX('Sheet15'[Comment date]),

        FILTER(

            'Sheet15',

            'Sheet15'[Ticket ID] = EARLIER('Sheet15'[Ticket ID]) &&

            'Sheet15'[Comment date] < EARLIER('Sheet15'[Comment date])

        )

    )

RETURN PrevDate

calculated column

Duration_Hours =

DATEDIFF('Sheet15'[Previous_Comment_Date], 'Sheet15'[Comment date], HOUR)

measure

Total_Hours_Per_Ticket =

CALCULATE(SUM('Sheet15'[Duration_Hours]), ALLEXCEPT('Sheet15', 'Sheet15'[Ticket ID]))

[Greg_Deckler]

@FelippeAzevedo7 I would suggest this pattern as a calculated column potentially. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
 ( __Current - __Previous ) * 1.