Distinct Values with date filter

[Kaskazi_Network]

Hi,

I would like some help in displaying distinct values for a specified period of time. 'DISTINCT COUNT' works seamlessly without a filter applied. For example let's say I would like to see distinct shops visited in week 35, I should be able to see ShopE only since ShopB and ShopD have been visited in week 29 and week 34 respectively.

Any advice how this can achieved?

Shop_id	Shop_name	Date_Visited	Week_No
100	ShopA	14/7/20	29
101	ShopB	15/7/20	29
102	ShopC	22/7/20	30
103	ShopD	17/8/20	34
100	ShopA	21/8/20	34
102	ShopC	13/8/20	33
106	ShopE	29/8/20	35
101	ShopB	23/8/20	35
103	ShopD	25/8/20	35
101	ShopB	14/9/20	38

[PaulDBrown · Accepted solution]

@Kaskazi_Network

Here is an alternative (table name 'ShopVisits'):
1) Measure to use in the filter panel to filter a table

Distinct visits = 
VAR PrevVisit = CALCULATETABLE(VALUES(ShopVisits[Shop_name]), 
                FILTER(ALL(ShopVisits[Week_No]), 
                ShopVisits[Week_No] < SELECTEDVALUE(ShopVisits[Week_No])))
VAR Selected = VALUES(ShopVisits[Shop_name])
RETURN
COUNTROWS(EXCEPT(Selected, PrevVisit))

2) measure to list the new stores visited:

New Shops visited = CONCATENATEX(FILTER(ALL(ShopVisits[Shop_name]), 
                    [Distinct visits] = 1), 
                    ShopVisits[Shop_name], ", ")

And you understand this:

[Syndicate_Admin]

That worked perfectly for me, thanks

[Greg_Deckler]

@Kaskazi_Network Maybe something like the following?

Measure = 
  VAR __Min = MIN('Table'[Date])
  VAR __Max = MAX('Table'[Date])
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table')[Date_Visited]>=__Min && [Date_Visited]>=__Max),"Shop_name",[Shop_name])))

[amitchandak]

@Kaskazi_Network , Try a measure like

Assume a week is selected

countx(values(Shop_id) , if(distinctcount(Table[Week_No]) ,calculate(distinctcount(Table[Week_No]) , filter(all(Table), Table[Week_No]<=selectedvalue(Table[Week_No])))<=0, blank(),1))

Best is that you move week to week table

and try like

countx(values(Shop_id) , if(distinctcount(Table[Week_No]) ,calculate(distinctcount(Table[Week_No]) , filter(all(Week), Week[Week_No]<=selectedvalue(Week[Week_No])))<=0, blank(),1))