count between dates by product

[ninakarsa]

Hi All,

I need a bit of help, trying to solve the following problem. I have 'Table A' with products with start and end dats, i have 'Table B' with sales by product. I want to create a table c as below which shows the the number of times a product appears in table a where the sales date from table b is between the start date and end date in table a.

I am using the following but it returns the results ignoring the product, results shown in table d:

Number= COUNTAX(filter(('table 1 (2)'),'table 1 (2)'[product] = [Product] && [sales date] >= 'table 1 (2)'[start Date]&& [sales date] <= 'table 1 (2)'[end date]),[Product])

Table A:

product	start Date	end date
long	01/03/2019	08/03/2019
long	01/03/2019	08/03/2019
medium	01/03/2019	08/03/2019
medium	01/03/2019	08/03/2019
medium	01/03/2019	08/03/2019
short	08/03/2019	15/03/2019
long	08/03/2019	15/03/2019
short	12/03/2019	19/03/2019
medium	08/03/2019	15/03/2019
medium	15/03/2019	22/03/2019

Table b

sales date	amount	Product
03/03/2019	120	long
03/03/2019	150	long
10/03/2019	165	short
17/03/2019	195
24/03/2019	210
31/03/2019	240

Table C:

sales date	amount	Product	number
03/03/2019	120	long	2
03/03/2019	150	long	2
10/03/2019	165	short	1
17/03/2019	195
24/03/2019	210
31/03/2019	240

table d:

sales date amount Product number

3 March 2019	120	long	5
3 March 2019	150	long	5
10 March 2019	165	short	3
17 March 2019	195		2
24 March 2019	210
31 March 2019	240

[Greg_Deckler · Accepted solution]

So I did the following. PBIX attached.

Table C = 
  ADDCOLUMNS(
      'Table B',
      "number",COUNTROWS(
          FILTER(
              'Table A',
              'Table B'[Product] = 'Table A'[product] && 
                'Table B'[sales date] >= 'Table A'[start Date] && 
                    'Table B'[sales date] <= 'Table A'[end date]
          )
      ))

[Greg_Deckler · Accepted solution]

So I did the following. PBIX attached.

Table C = 
  ADDCOLUMNS(
      'Table B',
      "number",COUNTROWS(
          FILTER(
              'Table A',
              'Table B'[Product] = 'Table A'[product] && 
                'Table B'[sales date] >= 'Table A'[start Date] && 
                    'Table B'[sales date] <= 'Table A'[end date]
          )
      ))

[ninakarsa · Accepted solution]

this works perfect. thank you