Forum Discussion

Olivier44's avatar
Olivier44
New Member
7 years ago
Solved

Count values for a specific date

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

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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])

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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])