Forum Discussion

ninakarsa's avatar
ninakarsa
Helper II
6 years ago
Solved

count between dates by product

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:

productstart Dateend date
long01/03/201908/03/2019
long01/03/201908/03/2019
medium01/03/201908/03/2019
medium01/03/201908/03/2019
medium01/03/201908/03/2019
short08/03/201915/03/2019
long08/03/201915/03/2019
short12/03/201919/03/2019
medium08/03/201915/03/2019
medium15/03/201922/03/2019

 

Table b

sales dateamountProduct
03/03/2019120long
03/03/2019150long
10/03/2019165short
17/03/2019195 
24/03/2019210 
31/03/2019240 

 

Table C:

sales dateamountProductnumber
03/03/2019120long2
03/03/2019150long2
10/03/2019165short1
17/03/2019195  
24/03/2019210  
31/03/2019240  

 

table d:

 

sales date amount Product number
3 March 2019120long5
3 March 2019150long5
10 March 2019165short3
17 March 2019195 2
24 March 2019210  
31 March 2019240  
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      ninakarsa
      Helper II
      6 years ago

      this works perfect. thank you