Finding dwell time

[Anonymous]

Hi, I am struggling to find dwell time with dax, would really appreciate if anyone could guide me on this. 

Sample dataset: 

DateTime	Type
1/1/2020 12:00	abc
1/1/2020 12:30	abc
1/1/2020 12:31	xyz
1/1/2020 13:30	xyz
1/1/2020 14:00	abc
2/1/2020 10:20	abc
2/1/2020 11:15	xyz
2/1/2020 11:16	abc
2/1/2020 13:15	efg
2/1/2020 16:00	xyz
3/1/2020 13:30	abc
3/1/2020 14:00	abc
3/1/2020 15:00	xyz
3/1/2020 15:15	xyz

This is what I want: 

DateTime	Type	Dwell time (min)
1/1/2020 12:00	abc	120
1/1/2020 12:30	abc	120
1/1/2020 12:31	xyz	61
1/1/2020 13:30	xyz	61
1/1/2020 14:00	abc	120
2/1/2020 10:20	abc	56
2/1/2020 11:15	xyz	285
2/1/2020 11:16	abc	56
2/1/2020 13:15	efg	1
2/1/2020 16:00	xyz	285
3/1/2020 13:30	abc	30
3/1/2020 14:00	abc	30
3/1/2020 15:00	xyz	15
3/1/2020 15:15	xyz	15

I am trying to find the dwell time for each type on daily basis, is there any possible way to do it? Have you ever seen anything like this? Would really appreciate some help! Thanks xoxo

[az38 · Accepted solution]

Hi @Anonymous 

try to create a measure

dwellTime = 
var _selectedDay = DATE(YEAR(SELECTEDVALUE('Table'[DateTime])), MONTH(SELECTEDVALUE('Table'[DateTime])),DAY(SELECTEDVALUE('Table'[DateTime])))
var _start = CALCULATE(MIN('Table'[DateTime]),FILTER(ALL('Table'),'Table'[Type]=SELECTEDVALUE('Table'[Type]) && DATE(YEAR('Table'[DateTime]),MONTH('Table'[DateTime]),DAY('Table'[DateTime]))=_selectedDay))
var _end = CALCULATE(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[Type]=SELECTEDVALUE('Table'[Type]) && DATE(YEAR('Table'[DateTime]),MONTH('Table'[DateTime]),DAY('Table'[DateTime]))=_selectedDay))
return
IF(_end>_start,datediff(_start,_end,MINUTE),1)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Thanks @az38 ! I guess this will definitely works on a smaller dataset. My dataset is a bit too huge and I have split it up to three parts and it is working fine now!

[Anonymous]

Hi @az38 , thanks for your reply but I am getting 1 min for each row instead of getting the dwell time. 😞 Is there any other possible way? Thanks for your help!

[az38]

Hi @Anonymous 

for me it works fine with your data sample

maybe you have something else in your data model? or your datetime field has no datetime data type?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38, thanks for your reply. My DateTime is definitely in DateTime format, but I just found out that this line didn't catch the date from DateTime, is there anything that went wrong?

var _selectedDay = DATE(YEAR(SELECTEDVALUE('Table'[DateTime])), MONTH(SELECTEDVALUE('Table'[DateTime])),DAY(SELECTEDVALUE('Table'[DateTime])))

[az38]

@Anonymous
Are you trying to create a measure?
Maybe you should just replace “,” to “;” due to localization question.
One more option - share your pbix file without sensitive data and give me a link to help you 🙂

[Anonymous]

I was trying to create a column for dwell time. 

The comma is working fine for me, but the semicolon is giving me errors. 

I am sorry I couldn't share the data due to privacy concerns, it looks exactly like the sample data but with seconds (e.g. 1/12/2020 01:12:25).

I've tried to create another column for "_selectedDay", but it returned blank, I thought it supposed to return the date?  

_selectedDay = DATE(YEAR(SELECTEDVALUE('Table'[DateTime])), MONTH(SELECTEDVALUE('Table'[DateTime])),DAY(SELECTEDVALUE('Table'[DateTime])))

[az38]

@Anonymous 

try to create new measure, not column

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

This is what I got from my data, the left table is the original data and the right table is the _selectedDay measure and dwellTime measure, and I really have no idea why this is not working. Please guide me on this, thanks a bunch! 

[az38]

Hi @Anonymous 

i do not know, it works fine, maybe you just didnt rename datetime to seentime in my statement?

here is my sample pbix-file with your data example https://ufile.io/wgzpm4jz 

do not hesitate to give a kudo to useful posts and mark solutions as solution