Forum Discussion

Frequent Visitor

9 years ago

Solved

DAX help - Invoices & customers

DAX newbie

Hi

I'm trying to update our customer table to indicate if they have ever purchased from a certain dept which is in the invoice table.

Based on the tables below I'm trying to do this

IsBookCustomer = IF invoices exist and invoices.salesdept = 'Books', Yes, No

Also it would be great to report the last invoice date as well

Customers

CustomeID, Name, IsBookCustomer, LastBookInvoiceDate

Invoices

InvoiceDate, SalesDept,InvoiceAmount

Thanks in advance

Tony

Datatouille
9 years ago
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
http://www.azeo.com/

4 Replies

Datatouille
Solution Sage
9 years ago
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 ?
- TonyR
  Frequent Visitor
  9 years ago
  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.
  - Datatouille
    Solution Sage
    9 years ago
    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
    http://www.azeo.com/