Forum Discussion
Solved
CountIFS to Calculate Payment Number
Hello,
I have a table (Table A) where I am storing a userID, productID and paymentDate. I would like to use something similar to a countifs statement from excel to add another column with the payment number based on the userID, productID and paymentDate. In excel it would look something like paymentNumber=countifs(userID, 'userID', productID, 'productID', paymentDate, =<'paymentDate'). How do I get do this in power BI to get the Table B below with the new paymentNumber column.
Table A
| userID | productID | paymentDate |
| a | B | 1/1/17 |
| a | B | 1/4/17 |
| a | A | 1/18/17 |
| a | A | 1/24/17 |
| a | B | 2/1/17 |
| b | A | 1/7/17 |
| b | B | 1/14/17 |
| b | A | 1/19/17 |
| b | B | 1/20/17 |
| b | B | 1/22/17 |
| c | A | 1/15/17 |
| c | B | 1/17/17 |
| c | A | 1/25/17 |
| c | B | 1/27/17 |
| c | B | 1/28/17 |
Table B
| userID | productID | paymentDate | paymentNumber |
| a | B | 1/1/17 | 1 |
| a | B | 1/4/17 | 2 |
| a | A | 1/18/17 | 1 |
| a | A | 1/24/17 | 2 |
| a | B | 2/1/17 | 3 |
| b | A | 1/7/17 | 1 |
| b | B | 1/14/17 | 1 |
| b | A | 1/19/17 | 2 |
| b | B | 1/20/17 | 2 |
| b | B | 1/22/17 | 3 |
| c | A | 1/15/17 | 1 |
| c | B | 1/17/17 | 1 |
| c | A | 1/25/17 | 2 |
| c | B | 1/27/17 | 2 |
| c | B | 1/28/17 | 3 |
jeffs9876,
In Power BI Desktop, create a calculated column using the following DAX.paymentNumber = CALCULATE(COUNTA(TableA[productID]),FILTER(TableA,TableA[userID]=EARLIER(TableA[userID])&&TableA[productID]=EARLIER(TableA[productID])&&TableA[paymentDate]<=EARLIER(TableA[paymentDate])))
Regards,
2 Replies
jeffs9876,
In Power BI Desktop, create a calculated column using the following DAX.paymentNumber = CALCULATE(COUNTA(TableA[productID]),FILTER(TableA,TableA[userID]=EARLIER(TableA[userID])&&TableA[productID]=EARLIER(TableA[productID])&&TableA[paymentDate]<=EARLIER(TableA[paymentDate])))
Regards,- Thank you
