Calculate Date Difference on two different rows

[GMelhouse]

I have a table of help ticket data.  The create date/time on a critical outage is on one row and the Resolution date/time is on a different row of the table.  There are also rows for all activities that take place with the ticket.    I want to calculate the hours between the create date and the resolution for each ticket that is a critical outage.  It seems like I need to group by the ticket ID and then compare the dates, but I can't figure out the right formula to do that.  I also thought I could create a new table that would group by ticket ID, but that wasn't working for me either.  I'm new to PowerBI so any help would be appreciated.

So and example of my data is :

Ticket ID	Create Date	End Date
123	3/10/2017 3:07:23 AM
123		3/10/2017 3:10:20 AM

[Anonymous · Accepted solution]

Hi @GMelhouse,

You can refer to below formulas to achieve your requirement.

A. Write a measure or calculated column to calculate the diff.

Measure:

Diff = 
var ticketID=LASTNONBLANK(Table2[Ticket ID],[Ticket ID])
return
DATEDIFF(LOOKUPVALUE(Table2[Create Date],Table2[Ticket ID],ticketID),LOOKUPVALUE(Table2[Create Date],Table2[Ticket ID],ticketID),SECOND)

Calculate column:

DateDiff = DATEDIFF(LOOKUPVALUE('Table 2'[Create Date],'Table 2'[Ticket ID],[Ticket ID]),LOOKUPVALUE('Table 2'[End Date],'Table 2'[Ticket ID],[Ticket ID]),SECOND) 

B. Summary table and get the diff.

Result = ADDCOLUMNS(SUMMARIZE(Table,[Ticket ID],"Create Date",MAX(Table[Create Date]),"End Date",MAX(Table[End Date])),"Diff",DATEDIFF([Create Date],[End Date],SECOND))

Regards,

Xiaoxin Sheng

[GMelhouse]

Thanks for your help.  The third one worked for me.