Forum Discussion
An odd count question
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.
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!!
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?
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 )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!
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.