How do I rank customer visits by date in PowerPivot?

Question

I have a table with customers' transactions named "purchases" with fields like this:

--------------------------------------------------
| title     | price |qty| client_id | created_at |
--------------------------------------------------
| product A |  100  | 1 |     1     | 01.01.2010 |
| product B |  120  | 2 |     1     | 05.01.2010 |
| product B |  120  | 1 |     2     | 08.01.2010 |

When I create a calc column for total purchase count, it works great:

=calculate(DISTINCTCOUNT([created_at]);ALLEXCEPT(purchases;purchases[client_id]))

but when I try to calculate the number of each exact customer visit (or rank) with the formula

=calculate(DISTINCTCOUNT([created_at]);filter(purchases;purchases[created_at]<=earlier([created_at]));ALLEXCEPT(purchases;purchases[client_id]))

it calculates the number of visit regadless the current client_id, it ignores the ALLEXCEPT part of the filter. How can I fix it?

I also tried to solve it with RANKX but the issue was similar: i don't know how to filter according to the current client_id.

Arseniy Arseniy · Accepted Answer · 2016-04-10T20:50:22

not sure how, but it works :))

=CALCULATE (DISTINCTCOUNT ( [created_at] );FILTER (ALL ( purchases ); [client_id] = EARLIER ( [client_id] ) && [created_at]<=EARLIER([created_at])))

got this hint on Facebook. hope it helps someone.

How do I rank customer visits by date in PowerPivot?

1 Answers