Forum Discussion

KingsleyZ's avatar
KingsleyZ
Frequent Visitor
8 years ago
Solved

Calculating DateDiff from different rows

Hello,

 

Let me start off by saying the included screenshot is FAKE data used for analysis for educational purposes.

 

Anyways, what i'm trying to accomplish is calulate the date differential between different rows.  What i'm trying to calculate is the duration between when a computer establishes 1st contact to last contact.  In the Dataset i've identified the computers from Source IP and then used RANKX function to group them all together.  In the Column "CountOf_SourceIP" one computer (or source IP) would represent 1 number.  For example all of the records with "73713" represent "one computer" somewhere in those records is the earliest date & the latest date.  Now i just need to calculate that and continue.  All the records with "73714" have an earliest date & latest.  

 

 

 

 

I just need to calculate the duration in between these records.

 

Thanks for your help

 

 

Need Help
Tutorial Requests
  • RahulYadav's avatar
    RahulYadav
    8 years ago

    Hi KingsleyZ,

    Try below solution.

     

    1. Add New Measure

    MinTimeStamp = CALCULATE(min('sorted-cowrie'[timestamp]),ALLEXCEPT('sorted-cowrie','sorted-cowrie'[source_ip]))

    2. Add one more measure.

    MaxTimeStamp = CALCULATE(MAX('sorted-cowrie'[timestamp]),
    ALLEXCEPT('sorted-cowrie','sorted-cowrie'[source_ip]))

    3. Add New Calculated Column

    Duration = DATEDIFF([MinTimeStamp],[MaxTimeStamp],SECOND)

    4. Add Final duration column. This will show duration in "Days HH:MM:SS" format.

    NewDuration = 
VAR TotalDuration='sorted-cowrie'[Duration]
VAR Dy =TRUNC(TotalDuration/3600/24)
VAR Hrs = TRUNC((TotalDuration-Dy*3600*24)/3600)
VAR Mns =TRUNC(MOD(TotalDuration,3600)/60)
VAR Scs = MOD(TotalDuration,60)
return IF(Dy=0,"",IF(Dy>1,Dy&" Days ",Dy&" Day "))&IF(Hrs<10,"0"&Hrs,Hrs)&":"&IF(Mns<10,"0"&Mns,Mns)&":"&IF(Scs<10,"0"&Scs,Scs)

    Below is the sample screenshot.

    https://www.dropbox.com/s/0udpc0urba4xebr/sorted-cowrie%202018-03-15_10-19-14.jpg?dl=0

     

    Please let me know if this helps.

     

    Thanks,

    Rahul

5 Replies

  • RahulYadav's avatar
    RahulYadav
    Resolver II
    8 years ago

    Hi KingsleyZ,

    Could you please attach sample data for us to better understand your requirement?

     

    Thanks,

    Rahul

    • KingsleyZ's avatar
      KingsleyZ
      Frequent Visitor
      8 years ago

      Hi, I've posted a copy up.

       

      When the link opens you will need to click Download up at the top 

  • RahulYadav's avatar
    RahulYadav
    Resolver II
    8 years ago

    Hi KingsleyZ,

    Try below solution.

     

    1. Add New Measure

    MinTimeStamp = CALCULATE(min('sorted-cowrie'[timestamp]),ALLEXCEPT('sorted-cowrie','sorted-cowrie'[source_ip]))

    2. Add one more measure.

    MaxTimeStamp = CALCULATE(MAX('sorted-cowrie'[timestamp]),
    ALLEXCEPT('sorted-cowrie','sorted-cowrie'[source_ip]))

    3. Add New Calculated Column

    Duration = DATEDIFF([MinTimeStamp],[MaxTimeStamp],SECOND)

    4. Add Final duration column. This will show duration in "Days HH:MM:SS" format.

    NewDuration = 
VAR TotalDuration='sorted-cowrie'[Duration]
VAR Dy =TRUNC(TotalDuration/3600/24)
VAR Hrs = TRUNC((TotalDuration-Dy*3600*24)/3600)
VAR Mns =TRUNC(MOD(TotalDuration,3600)/60)
VAR Scs = MOD(TotalDuration,60)
return IF(Dy=0,"",IF(Dy>1,Dy&" Days ",Dy&" Day "))&IF(Hrs<10,"0"&Hrs,Hrs)&":"&IF(Mns<10,"0"&Mns,Mns)&":"&IF(Scs<10,"0"&Scs,Scs)

    Below is the sample screenshot.

    https://www.dropbox.com/s/0udpc0urba4xebr/sorted-cowrie%202018-03-15_10-19-14.jpg?dl=0

     

    Please let me know if this helps.

     

    Thanks,

    Rahul

    • KingsleyZ's avatar
      KingsleyZ
      Frequent Visitor
      8 years ago

      Hi Rahul, Thank you!!! wow this was exactly what I was looking for :)  This is amazing!

  • KingsleyZ's avatar
    KingsleyZ
    Frequent Visitor
    8 years ago

    Hi

     

    Here's the link to one of the files... again this is FAKE data used for educational purposes

     

    https://1drv.ms/u/s!AquvLc-OSQ73gnZ0AadOx691nUwh

     

    What i'm trying to do is take all of the same Source_IP, then determine the earliest date that Source_IP is found in the file.  Then Determine the last Date that Source_IP is found in the file and the duration between these dates.  The Source_IP is not in order and the same value could be scattered throughout.  I was trying to create a Rank column by using RankX as i posted below but if anyone can help me do this in another way (if it's better) i'm open to that.  I am not asking to complete this for me but help me figure out how to do this.

     

    Thanks Power BI community :)