topic Re: Dynamic Unique Visitors in DAX Commands and Tips

Dynamic Unique Visitors

beaoliv123-_ — Mon, 21 Mar 2022 10:43:57 GMT

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 the number of unique visitors of that period; if I select only yestarday and today, I would like to see the new visitors from yesterday until now.

Thank you.

Re: Dynamic Unique Visitors

Whitewater100 — Fri, 18 Mar 2022 17:01:11 GMT

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.

Re: Dynamic Unique Visitors

beaoliv123-_ — Mon, 21 Mar 2022 10:41:19 GMT

Hi,

thanks for your help. I have a table with the customers ID and a Date table.

My goal is to change the way it calculates the unique visitors, based on the date filter. For example, if I select a week on the slicer, the calculation for the unique visitors considers 1 week, and the same for other selections.

Re: Dynamic Unique Visitors

Whitewater100 — Mon, 21 Mar 2022 15:49:44 GMT

Hi:

Do you think you could make up some sample data for the tables? The way your model is set up and relationships determines the calculation. If it looks like this (below) I could probably try to create mock data.

Re: Dynamic Unique Visitors

beaoliv123-_ — Mon, 21 Mar 2022 16:00:49 GMT

Table1

Date
User ID (each time a user comes in, the user ID appears)

Table2
Year
Quarter
Month
Day

My goal is to calculate the distinct user ID, but this measure must be dynamic with the date.

Ex.:
If I select a date between last week and today, then it will count each user ID once between these two dates. And depending on the date selection, I want the distinct count to change.

Re: Dynamic Unique Visitors

Whitewater100 — Mon, 21 Mar 2022 16:54:08 GMT

Hi:

I understand. I will make up some data to experiment with but only can look at it a bit later today. Thanks!

Re: Dynamic Unique Visitors

Whitewater100 — Tue, 22 Mar 2022 00:56:28 GMT

Hi:

Is this wha you are thinking? Hope it helps..https://drive.google.com/file/d/17_70PbE-6yGY0EtH3G8_cEF_iukgSM0C/view?usp=sharing

Re: Dynamic Unique Visitors

Anonymous — Thu, 24 Mar 2022 07:48:55 GMT

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.

Re: Dynamic Unique Visitors

beaoliv123-_ — Thu, 24 Mar 2022 14:15:17 GMT

Thank you all for your answers, you realy helped me solve the question.

Re: Dynamic Unique Visitors

Whitewater100 — Thu, 24 Mar 2022 14:33:48 GMT

Hi:

What didn't work on my previous solution? I understood you wanted to compare unique visitors..