Forum Discussion

Mathze03's avatar
Mathze03
Icon for Helper I rankHelper I
4 years ago

Calculation approach for returning visitors

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:

 

 

Need Help

5 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

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

    • Mathze03's avatar
      Mathze03
      Icon for Helper I rankHelper I
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • Mathze03's avatar
    Mathze03
    Icon for Helper I rankHelper I
    4 years ago

    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=102720375201131587663&rtpof=true&sd=true

     

    Thanks to Anonymous 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