Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Something like this
Measure = calculate(countrows(Table);Table[Model]="New")
I don't think that would give me the answer that I need it will give me 12 instead of 9
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
@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.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |