Forum Discussion
DAX help - Invoices & customers
Ok, well in that case here is what I suggest:
First, compute the last date of books purchase (if any) with this calculated column (named LastBookInvoiceDate):
= CALCULATE ( MAX ( Invoices[InvoiceDate] ) , Invoices[Sales Dept] = "Books" )
Then, use the column calculated above to check if the customer has ever bought a book:
=IF ( Customers[LastBookInvoiceDate] , "Yes", "No" )
Obviously you need to have a 1 to Many relationship between Customers and Invoices Tables and the calculated columns are both created from the Customers Table.
Feel free to ask any questions if necessary :)
Regards,
Tristan Malherbe
Data & BI Consultant at AZEO
Hi TonyR,
You need to have a relationship (or a link if you prefer) between 'Customers' and 'Invoices' tables to do so. It implies to have a common key (or column) between these 2 tables.
Do you also have CustomerId column in your 'Invoices' Table ?
Hi Datatouille
My mistake, the tables & fields were simple examples to illustrate the issue. There are common keys linked between the tables, so there is a CustomerID in the Invoice table.
Thx for pointing that out.
Ok, well in that case here is what I suggest:
First, compute the last date of books purchase (if any) with this calculated column (named LastBookInvoiceDate):
= CALCULATE ( MAX ( Invoices[InvoiceDate] ) , Invoices[Sales Dept] = "Books" )
Then, use the column calculated above to check if the customer has ever bought a book:
=IF ( Customers[LastBookInvoiceDate] , "Yes", "No" )
Obviously you need to have a 1 to Many relationship between Customers and Invoices Tables and the calculated columns are both created from the Customers Table.
Feel free to ask any questions if necessary :)
Regards,
Tristan Malherbe
Data & BI Consultant at AZEO