Count values for a specific date

[Olivier44]

Hi everyone, 

I have been searching for a solution for several days now but still couldn't find an answer to my issue. I hope you guys can help?

In my data source I have a list of assortment checks for thousands of stores over several years. Here is a simplified example of a table called "Detailed Assortment": 

 

I am trying to find the proper DAX function that will answer the following questions:

1/ How many products did the Customer A have in store at the end of 2017 ? The answer here would be 2 

I tried to add a new column with the following DAX function: 

Assortment last visit 2017 = CALCULATE(COUNT('Detailed Assortment'[Visit ID]),FILTER(MAXA('Detailed Assortment','Detailed Assortment'[Date])&&'Detailed Assortment'[Date].Year]=2017

But I got the total number of products that were in the stores in visit 1 and 2 (Total=5) when I should get only 2

2/ I would like to get a table with the summary of the visits displaying the following information: 

I hope this is clear. Thank you in advance for your time

[Greg_Deckler · Accepted solution]

You should be able to create a measure like:

Measure =
VAR __maxDate = MAX([Date])
VAR __table = FILTER('Table',[Date] = __maxDate && [Product in store] = TRUE())
RETURN
COUNTX(__table,[Product])

[Olivier44]

@Greg_Deckler thank you very much it seems to work!