Forum Discussion

TonyR's avatar
TonyR
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...
Need Help
  • Datatouille's avatar
    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/

Datatouille's avatar
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/

TonyR's avatar
TonyR
Frequent Visitor
9 years ago

Thx Datatouille 

Perfect Solution :)

Another education :)

Thx so much,

 

Tony