topic Re: How can I calculate the sum of the time between many updates from the same ticket ID. in DAX Commands and Tips

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

FelippeAzevedo7 — Tue, 25 Mar 2025 19:15:21 GMT

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.

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

Greg_Deckler — Tue, 25 Mar 2025 20:25:28 GMT

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/339586.
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.

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

techies — Tue, 25 Mar 2025 21:04:31 GMT

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

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

FelippeAzevedo7 — Mon, 31 Mar 2025 12:00:01 GMT

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.

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

MarkLaf — Tue, 01 Apr 2025 23:58:00 GMT

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

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

FelippeAzevedo7 — Thu, 03 Apr 2025 15:54:01 GMT

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

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

MarkLaf — Sat, 05 Apr 2025 20:38:05 GMT

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.