Forum Discussion

beaoliv123-_'s avatar
beaoliv123-_
Icon for Helper I rankHelper I
4 years ago
Solved

Dynamic Unique Visitors

Hi,  I would like to calculate the number of unique visitors based on the date range that the user decides to see. For example, if I select a date between last week and today, I would like to see th...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi beaoliv123-_ ,

     

    You may firstly refer to Whitewater100 's pbix.

     

    And I have created a data sample to test:

    And use CALENDAR() to create a Date table for slicer:

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

    Then create a measure:

    Count of Unique Users = CALCULATE(DISTINCTCOUNT('Table'[User ID]),FILTER('Table',[Date]>=MIN('For Slicer'[Date]) && [Date]<=MAX('For Slicer'[Date])))

    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.

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

Do you have a FacTable with visitor ID, date of visit and a Visitor lookup dimension table , along with a date table marked as such? That would be ideal for Power BI to answer your question.

 

What would be the period of increment for evaluation? e.g. Measuring back 28 days in daily increments or weekly increments? I'll paste a couple of images that help show how this type of analysis is best set up. You could have at least a Date and Customer dimension table (you may already but not evident from question)

 

This input helps the measure figure what you consider as lost or new and returning.

If you don't have all this, below is Jeffery Wang's (inventor of DAX) measure for new customers(vistors):

New Cust = vcustomersthis period = VALUES(Sales[CustKey])
var vfirstdatethisperiod = MIN(Sales[orderdate])
var vcustomersbefore = CALCULATETABLE(VALUES(Sales[custkey], 
                                       FILTER(ALL(Sales[orderdate]), [orderdate] < vfirstdatethisperiod, ALL(DATES))
return

COUNTROWS(EXCEPT(vcustomersthisperiod, vcustomersbefore))

 

With this measure if you have day in the visual column it will be based on day. If you have week, then it's week based and so on.