Forum Discussion
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:
Many Thanks in Advance,
Alex.
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
)
)
6 Replies
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
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?
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
)
)
