Forum Discussion
Get date delta between 2 rows
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
)
)
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
)
)
Great - okay so now I get a value, but they all have the value of 1 - is this a formatting issue?
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])
)
)