cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jeffs9876 Frequent Visitor
Frequent Visitor

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

userIDproductIDpaymentDate
aB1/1/17
aB1/4/17
aA1/18/17
aA1/24/17
aB2/1/17
bA1/7/17
bB1/14/17
bA1/19/17
bB1/20/17
bB1/22/17
cA1/15/17
cB1/17/17
cA1/25/17
cB1/27/17
cB1/28/17

 

 

Table B

userIDproductIDpaymentDatepaymentNumber
aB1/1/171
aB1/4/172
aA1/18/171
aA1/24/172
aB2/1/173
bA1/7/171
bB1/14/171
bA1/19/172
bB1/20/172
bB1/22/173
cA1/15/171
cB1/17/171
cA1/25/172
cB1/27/172
cB1/28/173

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: CountIFS to Calculate Payment Number

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

1.PNG

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

Re: CountIFS to Calculate Payment Number

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

1.PNG

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jeffs9876 Frequent Visitor
Frequent Visitor

Re: CountIFS to Calculate Payment Number

Thank you