Forum Discussion

GMelhouse's avatar
GMelhouse
Frequent Visitor
9 years ago
Solved

Calculate Date Difference on two different rows

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 t...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
GMelhouse
Frequent Visitor
9 years ago

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