Column with date of last purchase

[Thiagops]

Good morning,
I have 02 tables (One with sales and another with customers).

I need to include a column in the customer table with the date of the last purchase made by him
- The column I have in the 2 tables is CLI_CODIGO
- The column that has the date of sale is NS_DATA_EMISSAO

[tamerj1 · Accepted solution]

Hi @Thiagops 
Please try

Last Purchase Date =
MAXX ( RELATEDTABLE ( Sales ), Sales[NS_DATA_EMISSAO] )

[jaweher899]

You can achieve this by using a calculated column in the customer table that uses the MAX function to retrieve the latest date of purchase for each customer. Here's the DAX code for the calculated column:

Last Purchase Date = CALCULATE(MAX(Sales[NS_DATA_EMISSAO]), FILTER(Sales, Sales[CLI_CODIGO] = Customers[CLI_CODIGO]))

In this code, "Sales" is the table with sales data, "Customers" is the table with customer data, "NS_DATA_EMISSAO" is the date of sale column in the Sales table, and "CLI_CODIGO" is the column that links the two tables. The CALCULATE function evaluates an expression in a modified filter context, and the FILTER function returns a table with only the rows that meet the specified criteria. In this case, the FILTER function is used to filter the Sales table to only include the sales for the customer in the current row of the customer table. The MAX function is then used to find the maximum value of the NS_DATA_EMISSAO column in the filtered Sales table, which will be the latest date of purchase for the customer.