Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
alexmoller96
Regular Visitor

Get date delta between 2 rows

I am reading data from a flat log file, which shows the date and time that a device was connected / disconnected. The connect and disconnect entries are on 2 different rows. I want to work out the time difference between connect / disconnect so I can work out the usage time 

 

Eventually there will be more than one device IP and different connects / disconnects in the list so we need to make sure that is taken into account.

 

Is that something that is possible in PowerBI? 

 

Here is what the data looks like:

 

alexmoller96_0-1689781519645.png

 

Many Thanks in Advance,

Alex.

1 ACCEPTED SOLUTION

the issue could be with earlier. let's try without earlier. 

 

before trying this, make sure Ensure that the 'DateTime' column in your data is indeed of DateTime data type. If it is a string, the DATEDIFF function might not work as expected.

 

Verify that for each "Connect" event, there is a corresponding "Disconnect" event. If not, the calculation would not have a disconnect time to subtract from, and this may result in blank values.

 

lastly, check for case sensitivity. write exactly what is stored in your column. 

// Add an index column to 'YourTable' using Power Query
// You should add this column before loading the data into Power BI
Index = Table.AddIndexColumn('YourTable', "Index", 1, 1)

// DAX measure
Usage Time =
SUMX(
FILTER(
'YourTable',
'YourTable'[Event] = "Disconnect"
),
VAR CurrentIndex = 'YourTable'[Index]
VAR CurrentDevice = 'YourTable'[DeviceIP]
VAR ConnectTime =
CALCULATE(
MAX('YourTable'[DateTime]),
FILTER(
'YourTable',
'YourTable'[Event] = "Connect" &&
'YourTable'[DeviceIP] = CurrentDevice &&
'YourTable'[Index] < CurrentIndex
)
)
RETURN
DATEDIFF(
ConnectTime,
'YourTable'[DateTime],
MINUTE
)
)

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

6 REPLIES 6
rubayatyasmin
Super User
Super User

Hi, @alexmoller96 

 

try using date diff and minx

 

example code

 

Usage Time = SUMX(
FILTER(
'YourTable',
'YourTable'[Event] = "Disconnect"
),
DATEDIFF(
MINX(
FILTER(
'YourTable',
'YourTable'[Event] = "Connect" &&
'YourTable'[DeviceIP] = EARLIER('YourTable'[DeviceIP]) &&
'YourTable'[DateTime] < EARLIER('YourTable'[DateTime])
),
'YourTable'[DateTime]
),
'YourTable'[DateTime],
MINUTE
)
)

 

also this thread should be helpful

 

Solved: Re: Show text for date range between 45 days and 5... - Microsoft Fabric Community

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Hi @rubayatyasmin, thanks a lot for your reply. I tried using the code that you supplied but dont get any values, am I missing something? 

alexmoller96_0-1689845465449.pngalexmoller96_1-1689845485489.png

 

the issue could be with earlier. let's try without earlier. 

 

before trying this, make sure Ensure that the 'DateTime' column in your data is indeed of DateTime data type. If it is a string, the DATEDIFF function might not work as expected.

 

Verify that for each "Connect" event, there is a corresponding "Disconnect" event. If not, the calculation would not have a disconnect time to subtract from, and this may result in blank values.

 

lastly, check for case sensitivity. write exactly what is stored in your column. 

// Add an index column to 'YourTable' using Power Query
// You should add this column before loading the data into Power BI
Index = Table.AddIndexColumn('YourTable', "Index", 1, 1)

// DAX measure
Usage Time =
SUMX(
FILTER(
'YourTable',
'YourTable'[Event] = "Disconnect"
),
VAR CurrentIndex = 'YourTable'[Index]
VAR CurrentDevice = 'YourTable'[DeviceIP]
VAR ConnectTime =
CALCULATE(
MAX('YourTable'[DateTime]),
FILTER(
'YourTable',
'YourTable'[Event] = "Connect" &&
'YourTable'[DeviceIP] = CurrentDevice &&
'YourTable'[Index] < CurrentIndex
)
)
RETURN
DATEDIFF(
ConnectTime,
'YourTable'[DateTime],
MINUTE
)
)

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Great - okay so now I get a value, but they all have the value of 1 - is this a formatting issue?

alexmoller96_0-1689848797517.png

alexmoller96_1-1689848841927.png

 

 

Okay - I think I understand after looking at DATEDIFF - If I use SECOND instead of MINUTE, it gives me the number of seconds that the device was in use for - thanks a lot!!!!

 

@rubayatyasmin subsequent question - is there a way that I can filter this by date so I can get a value for usage time by day

Good to know that it worked. 

 

would appreciate it if you accept it as a solution. 

 

And allow me some time I will get back to you with your subsequent question. 

 

and to add filter 

Usage Time by Day =
CALCULATE(
[Usage Time],
FILTER(
ALL('YourTable'),
'YourTable'[Date] >= MIN('YourTable'[Date]) &&
'YourTable'[Date] <= MAX('YourTable'[Date])
)
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors