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