Forum Discussion
An odd count question
Hi All:
Happy Friday!
i have an odd question. here is my table below. i want to create a column that count how many times a customer show up
For example, customer A purchased 18 units on Jan 1st 2021, so that would be 1, and then customer A purchased again on 5/6/2021, so that count as 2 and Customer A showed up again in Sep, so that's 3. ( The result column is what i am looking for).....anyone could help me? thank you!!
| Customer ID | Order Date | Purchase Qty | Result |
| A | 1/1/2021 | 18 | 1 |
| B | 1/15/2021 | 5 | 1 |
| C | 3/4/2021 | 12 | 1 |
| D | 4/5/2021 | 22 | 1 |
| A | 5/6/2021 | 16 | 2 |
| B | 3/2/2021 | 5 | 2 |
| C | 5/7/2021 | 12 | 2 |
| A | 9/4/2021 | 9 | 3 |
| D | 8/1/2021 | 5 | 2 |
| B | 10/8/2021 | 7 | 3 |
Hi leilei787
Actually now it is much easier. Check out the updated file https://www.dropbox.com/t/gnQXLqmohiDQaOqd
You just need two measuresFirst Purchase Date = CALCULATE ( MIN (Data[Order Date] ), Data[Amount] > 0 )Last Purchase Date = CALCULATE ( MAX (Data[Order Date] ), Data[Amount] > 0 )The CALCULATE is only required to restrict the calculation on real sales transactions otherwise a simple MAX/MIN would be enough and the filter context will take care of the rest. You just need to place the Customer at the rows of the matrix visual and the Products ( either two or more) on the columns. Then place the two measures at the values, and that's it.
8 Replies
leilei787 Try:
QtyResult Column = VAR __Customer = [Customer ID] VAR __Date = [Order Date] RETURN COUNTROWS(FILTER('Table',[Customer ID] = __Customer && [Order Date] <= __Date))thanks Greg! The formula works! However, i need to revise my question after deeper look at the project. so here is what i am looking for ( see below table).
i have a list of customers with order date ( some order dates are the same because customer order different product). i am trying to identify a customer purchased a product the 1st time or 2nd time.
For example, Customer A ordered both product X and Z the 1st time on 1/1/2021, and then Customer A purchased product X and Z again ( 2nd order) on 5/6/2021, and 3rd order on product X in Sep
Order # can be number 1,2,3 instead of 1st, 2nd order
Thank you again!
Customer ID Order Date Product Purchase Qty Order # A 1/1/2021 x 18 1st order A 1/1/2021 z 5 1st order B 1/15/2021 x 5 1st order C 3/4/2021 x 12 1st order C 3/4/2021 y 42 1st order D 4/5/2021 X 22 1st order A 5/6/2021 x 16 2nd order A 5/6/2021 Z 7 2nd order B 3/2/2021 X 5 2nd order C 5/7/2021 Y 12 2nd order A 9/4/2021 X 9 3rd order D 8/1/2021 X 5 2nd order B 10/8/2021 X 7 3rd order
Hi leilei787
Here is the sample file with the solution https://www.dropbox.com/t/fvtEBetKhYgSu2a3
The calculated column formula isResult = "Order " & RANKX ( FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ), Data[Order Date],, ASC, Dense )Hi Tamerj1
Appreciate for your solution. two additional question: how to setup a condition in your formula that rank the order only if amount >0....so if customer A placed a Demo order, because the value =$0, we do not count as an order....it has to be a real order with amount >$0
Another question is...i want to create another column that identify first order and last order.....so in the example you provide, the 1st order would be first order.....the 3rd order would be the last order....but a customer would place many orders as time go on....it is easy to capture the first, but how to identify the last one?
thank you!!
