Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Finding dwell time

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/...
Need Help
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    az38
    Community Champion
    6 years ago
    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        try to create new measure, not column

         

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