Forum Discussion

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
10 years ago
Solved

Find Latest Transaction Date for Each Customer

I am trying to accomplish several things here - but the first goal is to determine the most recent or last transaction for each customer.    I have tried about 8 different things but fall a little ...
  • kcantor's avatar
    kcantor
    10 years ago

    I have successfully used LASTDATE.

    Customer Last Transaction = LASTDATE('Sales'[OrderDate])

    Then I populate the rows with the customer ID. My sales table is matched to my customer table using the ID.

     

  • Sean's avatar
    Sean
    10 years ago

    heathernicole I have not read the whole post but give this a try...

     

    Last Transaction =
CALCULATE (
    LASTDATE ( 'Sales Details'[SalesTxnTimeModified] ),
    ALLEXCEPT ( 'Customer', 'Customer'[Customer Name] )
)
  • Sean's avatar
    Sean
    10 years ago

    Hello heathernicole! I don't know what to tell you???

     

    I use this exact Measure to calculate Last Payment by Purchaser - and there are MANY MANY duplicate dates!

     

    I just tried it and it works as a Calculated Column as well.

     

    You know the May Update was released today - have you updated? (Even though this should not be the issue!)

    https://powerbi.microsoft.com/en-us/blog/

     

     

     

  • Sean's avatar
    Sean
    10 years ago

    heathernicole Try this...

     

    Go to Modeling Tab => Click New Table button => type this...

     

    Summary Table =
SUMMARIZE (
    'Sales Details',
    'Sales Details'[Customer ID],
    "Last Transaction", MAX ( 'Sales Details'[SalesTxnTimeModified] )
)
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

heathernicole what is the relationship between these 3 tables?

 

First you were using 'Sales Details' and 'Customer'

 

Now it seems you are using "Calendar - Transaction Date' and 'Customer'

 

Where is the Last Date you are looking for in 'Sales Details' or 'Calendar - Transaction Date'?

 

And how are these 3 tables related?

 

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
10 years ago

Create the calculated column in Customers.

 

CustomerLastDateTransaction = CALCULATE(LASTDATE('Calendar-Transact'[TransactionDate]);ALLEXCEPT('Customer';'Customer'[CustomerAccount]))

 

Or

 

Created in Sales Details the calculated column should be:

 

CustomerLastDateTransaction = CALCULATE(LASTDATE('Calendar-Transact'[TransactionDate]);ALLEXCEPT('SalesDetails';'SalesDetails'[CustomerAccount]))

 

 

Please try and Feedback