Count Distinct by Date

[Anonymous]

Hi,

I want to find the distinct count of customers who have visited a shop everyday. Is there a specific DAX or Mquery?

My plan is to create a column chart to find unique customers per day grouped monthly.  

Eg: "Sam" visits Wmart today thrice and tomorrow twice. I want him to be counted once today and again counted once tomorrow. Similarly for all customers and then add the values monthly.

Would appreciate if anyone could show me how to do it.

Regards,

Ruban 

[fsadiq]

Hello All, I have a list of dates and Names. Each name has random dates based on their daily activities i.e A name has like 3 same date because he/she carried out 3 activities that same day . I already sorted the datasets by month using the slicer viz. I want to count all unique dates in a month i.e When you select may, I want to have unique number of days in may using Card Viz. Viz: https://ibb.co/sCm2dkP Table: https://ibb.co/1JqhgRR I made the Month sorting using end column. Thanks

[v-lili6-msft · Accepted solution]

hi, @Anonymous @ryan_mayu

Eg: "Sam" visits Wmart today thrice and tomorrow twice. I want him to be counted once today and again counted once tomorrow. Similarly for all customers and then add the values monthly.

So times of Sam visits Shop these two days is 2, not just distinctcount result is 1.

You could try to use DISTINCTCOUNT and SUMX to create a measure like this

Measure 2 = var _table=SUMMARIZE('Table','Table'[Shop],'Table'[Date],"distinctcount",DISTINCTCOUNT('Table'[Customer]))
return
SUMX(_table,[distinctcount])

Result:

Regards,

Lin

[Anonymous]

Hello @v-lili6-msft,

To go further, is it possible to calculate an average of this measure. A weekly average taking into account the working days

thank you

[Anonymous]

Thanks Lin. Sorry for the delay in response. Your formula worked.

[ryan_mayu]

@Anonymous 

Hi, why distinctcount formula doesnot work in this situation? Have you tried about that? Or you have some specific requirement?

Hope this is helpful.

Thanks and BR

Ryan

[parry2k]

@Anonymous can you provide sample data and will get you the solution