Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

New & Returning Users

Completely new to DAX, I have to define two metrics, trending with time, with the following data structure.

 

 

Conversation IDUser IDTimeStamp
123SamMarch 10, 2020 23:45:00
123SamMarch 10, 2020 23:59:00
456VimesDecember 11, 2019 12:34:00
789VimesDecember 12, 2019 07:36:00
567VimesJanuary 10, 2020 10:34:00

 

"New Users" - Any user who visits the page for the FIRST time. (i.e, has not visited before). This user should be considered "new" for a period of 20 hours. For example, if Sam visits the page multiple times within 20 hours, he is a new user. 20 hours after his first (earliest timestamp), he is not new anymore. However, if the time elapsed between this 20 hour period and his next visit is greater than 90 days, he becomes a "new user" again.

 

"Returning Users" - Any user who visits the page after the aforementioned 20 hour "new" period. If Sam visits the page again after the 20 hour period, he becomes a returning user. Again, if the time elapsed between this 20 hour period and his next visit is greater than 90 days, he becomes a "new user" again.

 

I have so far been able to add a column to define the duration (hours or days) elapsed between a user's current and previous visit, but cannot think past that.

 

Thank you!