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] )
)
heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
10 years ago

SeanHad to change SalesTxnTimeModifed to a Date data type. It was orginally a DateTime data type. 

 

I'm thnking that's what caused the issue. :) 

 

On my way to phase 2 of this report. THANK YOU!

 

kcantor - I believe your solution was correct too - I think the DATETIME was messing it up.

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

Sean kcantor -

 

Well - I got the function to work - but it's not pull the proper data. The items grouped in red are the same customer. What it appears to be doing is pulling EACH transaction for the customer; NOT the LAST transaction. So I'm not sure what it's doing. 

 

I've tried all of the suggestions from yesterday. None of them give errors but all of them are producing the same results it seems. I've also tried switching out MAX for LASTDATE.

 

Customer Last Transaction

 

Any ideas or thoughts about what might be the problem? I'm not sure what to do. I've tried using the LAST transaction date, the LAST salesTxnTime created, Time modified - I'm thinking it has to do with how Quickbooks handles data but I'm not entirely sure. arify Eric_Zhang - any thoughts or input you have is greatly appreciated! :) 

  • kcantor's avatar
    kcantor
    Icon for Community Champion rankCommunity Champion
    10 years ago

    heathernicole Are you using this as a measure or as a calculated column? I have one application of this where I add it as a calculated column in my customer table. that way is pulls a single date per customer from the sales fact table to the customer table. Perhaps this method would work for you?

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

      kcantor I am using a calculated column - not a measure. I wonder if a measure WOULD work better in this case. Although I'm not sure why it would. 

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

        Maybe using this:

         

        Calculated Column

         

        CustomerLastDateTransaction = CALCULATE(LASTDATE('Transaction'[TransactionDate]);ALLEXCEPT('Transaction';'Transaction'[CustomerName]))