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 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 IDCreate DateEnd Date
1233/10/2017 3:07:23 AM 
123 3/10/2017 3:10:20 AM

 

  • 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

2 Replies

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