Reply

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

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.

 

 

FelippeAzevedo7_0-1742930075853.png

 

 

Many tks for the supporting.

1 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):

MarkLaf_0-1743542725322.png

 

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

 

 

MarkLaf_1-1743551753964.png

 

View solution in original post

6 REPLIES 6

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.

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

MarkLaf_0-1743542725322.png

 

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

 

 

MarkLaf_1-1743551753964.png

 

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

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
Solution Sage
Solution Sage

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]))
― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)