Calculating DateDiff from different rows

[KingsleyZ]

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

[RahulYadav · Accepted solution]

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]

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

[KingsleyZ]

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 🙂

[RahulYadav]

Hi @KingsleyZ,

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

Thanks,

Rahul

[KingsleyZ]

Hi, I've posted a copy up.

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