CountIFS to Calculate Payment Number

[jeffs9876]

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

[Anonymous · Accepted solution]

@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,

[jeffs9876]

Thank you