An odd count question

[leilei787]

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

[tamerj1]

Hi @leilei787 
Here is the sample file with the solution https://www.dropbox.com/t/fvtEBetKhYgSu2a3
The calculated column formula is

Result = 
"Order " &
RANKX ( 
    FILTER (
        Data,
        Data[Customer ID] = EARLIER ( Data[Customer ID] )
    ),
    Data[Order Date],,
    ASC,
    Dense
)

[leilei787]

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!!

[tamerj1]

@leilei787 

Regarding your first question:

Result = 
IF (
    Data[Amount] > 0,
    RANKX ( 
        FILTER (
            Data,
            Data[Customer ID] = EARLIER ( Data[Customer ID] )
        ),
        Data[Order Date],,
        ASC,
        Dense
    )
)

Regarding yiu 2nd question. Please clarify. Do you want to flag the last sale and blank out othes?

[tamerj1]

Last Sale = 
VAR LastDate =
IF (
    Data[Amount] > 0,
    MAXX ( 
        FILTER (
            Data,
            Data[Customer ID] = EARLIER ( Data[Customer ID] )
        ),
        Data[Order Date]
    )
)
RETURN
IF ( Data[Order Date] = LastDate, TRUE )

[leilei787]

Hi Tamerj1

again, thank you for your help! so just to clear what i am looking for. here is the story, we have product A and B. (A is the system, B is an accessory). Customers have to purchase product A first, then they can purchase B.....we are trying to see first and Last purchase date for A and B in certain period, then we can calculate the attach rate % for that period. 

this is the end result: (a summary table in Power BI to show each customer, what is first purchase date for A and B, what is the last purchase date for A and B.

Customer ID	first purchase date(Product A)	last purchase date(Product A)	first purchase date (Product B)	last purchase date (Product B)
100	1/1/2021	6/23/2021	3/6/2021	8/17/2021
200	2/4/2021	8/6/2021	2/25/2021	4/10/2021

i think in order to show this, we need calculate column. here is the new sample data

The last 3 columns ( Result, first order date, last order date are what i am hoping to see)

Customer ID	Order Date	Product	Purchase Qty	Result	First Order Date	Last Order Date
100	1/1/2021	A	18	First Time Order of A	1/1/2021
100	1/1/2021	C	10
200	2/4/2021	A	12	First Time Order of A	2/4/2021
200	2/25/2021	B	22	First Time Order of B	2/25/2021
100	3/6/2021	B	16	First time order of B	3/6/2021
100	4/7/2021	A	12
200	4/10/2021	B	9	Last Time Order of B		4/10/2021
100	5/8/2021	A	7
100	5/17/2021	B	2
100	6/23/2021	A	366	Last Time Order of A		6/23/2021
100	6/19/2021	B	89
100	7/25/2021	B	9
200	8/6/2021	A	34	Last Time Order of A		8/6/2021
100	8/17/2021	B	89	Last time order of B		8/17/2021

i think the formula you provide works partially...we can blank 2nd, or 3rd order...only identify 1st and 2nd....please let me know if you could modify last formula. i do appreciate your time!

[tamerj1 · Accepted solution]

Hi @leilei787 
Actually now it is much easier. Check out the updated file https://www.dropbox.com/t/gnQXLqmohiDQaOqd
You just need two measures

First 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.

[Greg_Deckler]

@leilei787 Try:

QtyResult Column = 
  VAR __Customer = [Customer ID]
  VAR __Date = [Order Date]
RETURN
  COUNTROWS(FILTER('Table',[Customer ID] = __Customer && [Order Date] <= __Date))

[leilei787]

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