countrows with conditions in other column

[rbeneteli]

Hello, 

I have a table with data like this:

Code	Seller	Model	Value	Date
1234	A	New	10/1/18	100
1235	A	New	10/1/18	80
1236	A	New	10/1/18	90
1237	A	New	10/1/18	80
1238	A	New	10/1/18	100
1239	A	New	10/1/18	90
1240	A	New	10/23/18	80
1241	A	New	10/26/18	100
1242	A	New	10/27/18	70
1243	A	New	10/28/18	100
1244	A	New	10/28/18	100
1242	A	Residual	10/25/18	30
1246	A	New	10/30/18	80
1240	A	Residual	11/1/18	20
1235	A	Residual	11/1/18	20
1236	A	Residual	11/24/18	10
1237	A	Residual	11/28/18	20
1246	A	Residual	11/28/18	20

I have to count the numbers of sellings that seller A had in October, for this I can count the lines where Model is equal to New, the problem is my selling value is 100, but some times this selling could be done with another value like 80, and the buyer have until the 24th day of the next month to pay the residual value. If he pays in a date like 11/28/18 it does not count as a sale for seller A. As you can see in this table, seller A has 9 sales in october, beacause the sales with the code 1237 and 1246 are payed after the due date and the sale with the code 1239 does not have a residual payment.
How can I do that?

[rafaelmpsantos]

Something like this

Measure = calculate(countrows(Table);Table[Model]="New")

[rbeneteli]

I don't think that would give me the answer that I need it will give me 12 instead of 9

[rafaelmpsantos]

But you have 12 new sales in October... let count.

1 - 1234 A New 10/1/18 100
2 - 1235 A New 10/1/18 80
3 - 1236 A New 10/1/18 90
4 - 1237 A New 10/1/18 80
5 - 1238 A New 10/1/18 100
6 - 1239 A New 10/1/18 90
7 - 1240 A New 10/23/18 80
8 - 1241 A New 10/26/18 100
9 - 1242 A New 10/27/18 70
10 - 1243 A New 10/28/18 100
11 - 1244 A New 10/28/18 100
12 - 1246 A New 10/30/18 80

[rbeneteli]

@rafaelmpsantos Hi, I appreciate your answer, but you didn't understand what is my objective in this situation, I only can count a sale if its value is equal to 100 or if it's residual value are paid before 11/24, as example I have the sale with the code 1239 its value is equal to 90 and I do not have a residual value paid  befora the due date, so it will not count as a sale for me.

[rafaelmpsantos]

Ok now i see the point

This works for me

COUNT =
VAR MAXDATE = DATE(2018,11,24)
return
SUMX(CALCULATETABLE(ADDCOLUMNS(DISTINCT(Table[Code]),"VALUE",IF(CALCULATE(SUM(Table[Value]),Table[Date]<=maxdate)=100,1,0))),[VALUE])