Forum Discussion
EvertonRamone
Helper I
Helper IDAX - Customer first purchase
I need a measure wich returns me the first purchase of customers who bought product B (key product 2)
This is my data model
| FactSales | |||
| KeyDate | KeyCustomer | KeyProduct | Total |
| 1 | 1 | 1 | 12,9 |
| 1 | 2 | 2 | 13 |
| 1 | 3 | 1 | 156,4 |
| 1 | 4 | 1 | 564,8 |
| 2 | 1 | 1 | 894,8 |
| 2 | 2 | 1 | 56,5 |
| 3 | 1 | 2 | 564,85 |
| 3 | 2 | 3 | 564,8 |
| 4 | 1 | 1 | 1325,6 |
| 4 | 2 | 1 | 132,3 |
| Customer | |
| KeyCustomer | Name |
| 1 | Jean |
| 2 | Mari |
| 3 | Lisa |
| 4 | Julian |
| 5 | Jhonny |
| Calendar | |
| KeyDate | Date |
| 1 | 01/01/2018 |
| 2 | 02/01/2018 |
| 3 | 01/05/2018 |
| 4 | 01/08/2018 |
| Product | |
| KeyProduct | Product |
| 1 | A |
| 2 | B |
| 3 | C |
Hi EvertonRamone,
Create a measure using DAX as below:
Result = VAR KeyDate = CALCULATE(MIN('Calendar'[KeyDate]), FILTER('Calendar', 'Calendar'[Date] = MIN('Calendar'[Date]))) VAR KeyProduct = CALCULATE(MIN('Product'[KeyProduct]), FILTER('Product', 'Product'[Product] = "B")) RETURN CALCULATE(MIN('Customer'[Name]), FILTER('FactSales', 'FactSales'[KeyDate] = KeyDate && FactSales[KeyProduct] = KeyProduct))Regards,
Jimmy Tao
2 Replies
v-yuta-msftCommunity Support
Hi EvertonRamone,
Create a measure using DAX as below:
Result = VAR KeyDate = CALCULATE(MIN('Calendar'[KeyDate]), FILTER('Calendar', 'Calendar'[Date] = MIN('Calendar'[Date]))) VAR KeyProduct = CALCULATE(MIN('Product'[KeyProduct]), FILTER('Product', 'Product'[Product] = "B")) RETURN CALCULATE(MIN('Customer'[Name]), FILTER('FactSales', 'FactSales'[KeyDate] = KeyDate && FactSales[KeyProduct] = KeyProduct))Regards,
Jimmy Tao
EvertonRamonev-yuta-msft, Maybe I expressed myself wrong.
I meant I need to now every date of first purchase by customer
Customer A - 20/01/2018
Customer B - 14/01/2018
Customer C- 22/01/2018