Calculation approach for returning visitors

[Mathze03]

Dear forum,

We use Power BI to analyze the usage of our performance support system. I'm seeking some advice on how to calculate / identifying the returning visitors. We get a CSV file from the system, which holds the information for each "page" visit. This comprises a date stamp, a session ID and an the corresponding unique user ID beside other info. 

We want to identy which of the unique users did return to the page within a certain time frame. I'm struggeling to get this done within Power BI. Is there a way to compare data with a formular or a filtered list? Or do I have to use aggregation? During my web search I learned that Google Analytics does this automatically, but we cannot use Google Analytics as we use Power BI (what I actuall appreciate 🙂 ). However, my limited skills prevent me to get this calculation done.

Has anyone expereices and can please give me hint on how to make this happen? 

Here is an extraction of the data I have on hand:

[Mathze03]

Dear both,

thanks a lot for your help on this topic. I tried ti use @amitchandak  way, buit didn't get any results. So it seems like that I did something. Instead of Sales, I have created a metric that counts all UniqueUser IDs within the table TopicView.  I have used this metric for the Rolling formulas:

Count of UU = count(TopicView[UniqueUserID])

Rolling 3 = CALCULATE([Count of UU],DATESINPERIOD('Date'[Date], ENDOFMONTH('Date'[Date]),-13,MONTH))

Rolling 9 before three = CALCULATE([Count of UU],DATESINPERIOD('Date'[Date],EOMONTH(MAX('Date'[Date]),-3),-9,MONTH))

Rolling 12 = CALCULATE([Count of UU],DATESINPERIOD('Date'[Date], ENDOFMONTH('Date'[Date]),-12,MONTH))

New user this period = SUMX(VALUES(TopicView[UniqueUserID]),IF(ISBLANK([Rolling 9 before three]) && NOT(ISBLANK([Rolling 3])),1,BLANK()))

Lost user this period = SUMX(VALUES(TopicView[UniqueUserID]),if(ISBLANK([Rolling 3]) && NOT(ISBLANK([Rolling 9 before three])),1,BLANK()))

Retained User This Period = SUMX(VALUES(TopicView[UniqueUserID]),IF(NOT(ISBLANK([Rolling 3])) && NOT(ISBLANK([Rolling 9 before three])),1,BLANK()))

I follwed your advice and have cleaned upt the file in question and uploaded it so that you can see what I have available. You can access it via this link:

https://docs.google.com/spreadsheets/d/1i1znEr7Q_ZtEDuDYNjp3HYvwhx_PkZR0/edit?usp=sharing&ouid=10272...

Thanks to @v-eqin-msft for showing me how this can be done.

Could you please kindly have a look at my approach and formulas and check what I did wrong?

Thanks a lot in advance.

Mathias

[v-eqin-msft]

Hi @Mathze03 ,

Yes, you could upload some unsensitive data and expected output to help us clarify your scenario. Please refer to:

How to provide sample data in the Power BI Forum  

How to Get Your Question Answered Quickly

If you want to get the users in a selected date period, I have created a data sample:

Firstly, please add a calendar table for slicer to select date period:

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Then create a measure:

Combine distinct users = 
var _disUsers=DISTINCT( SUMMARIZE(FILTER(ALLSELECTED('Table'),[Date]>=MIN('Calendar'[Date]) && [Date]<=MAX('Calendar'[Date])),[User]))
return CONCATENATEX(_disUsers,[User],",")

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Mathze03 , It should be similar to customer retention, you need work on range

Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

[Mathze03]

Dear Amit, thanks a lot for your quick reply! I set up that additonal date table. I struggle to get the "rolling" dax formula set up. It seems like that the term CALCULATE requires as another measure, which I don't have. I only have the columns Unique User ID, Date, Session ID and Session Duration. (I have others as well, but I think they don't matter here....). Can I upload an example file here? Kind regards, Mathias

[mh2587]

if you just provide dummy data and expected output it will ease our approach to this problem