cancel
Showing results for
Did you mean:
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

 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

1 ACCEPTED SOLUTION

Accepted Solutions
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]))) `

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

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

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.
Highlighted
Frequent Visitor

Re: CountIFS to Calculate Payment Number

Thank you

Announcements

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 132 members 1,817 guests
Recent signins: