DAX Time compare (same column next row)

athomp15 · ‎05-08-2019

Hi All, I've solved this issue in Power Query, but would prefer a DAX solution. I've tried a EARLIER function, but can't quite get the results I need. Anyhow I have a table that looks like the following. What I would like to do is to calculate the time difference between each page access.

user	page url	date/time accessed
joe	page 1	12/11/2019 10:00:01
fred	page 1	12/11/2019 10:00:01
joe	page 3	12/11/2019 10:00:04
joe	page 4	12/11/2019 10:00:05
fred	page 5	12/11/2019 10:00:07
fred	page 6	12/11/2019 10:00:08
fred	page 7	12/11/2019 10:00:21
joe	page 8	12/11/2019 10:00:22
fred	page 9	12/11/2019 10:00:22
joe	page 10	12/11/2019 10:00:30

So when in a Table visual I get something along the lines of

user	page url	date/time accessed	Time on page (s)
joe	page 1	12/11/2019 10:00:01	3
joe	page 3	12/11/2019 10:00:04	1
joe	page 4	12/11/2019 10:00:05	1
joe	page 8	12/11/2019 10:00:22	17
joe	page 10	12/11/2019 10:00:30

Any help would be greatly appreciated.

Thanks

Alex

Mariusz · ‎05-09-2019

Hi @athomp15

Is the below Measure any help?

Measure = 
VAR mdt = MAX(YourTable[date/time accessed])
RETURN 
DATEDIFF(
    mdt,
    CALCULATE(
        MIN(YourTable[date/time accessed]),
        ALLEXCEPT(YourTable, YourTable[user]),
        YourTable[date/time accessed] > mdt
    ),
    SECOND
)

Hope this helps