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
Icon for Community Champion rankCommunity Champion
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.

 

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

kcantor - Hello! Hope you had a good weekend!

 

I tried LastDate on the Transaction Date Modified - and this is the error I got

 

A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.The current operation was cancelled because another operation in the transaction failed. 

If I use this formula on the Calendar Table - no problem

 

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

    That is odd. I use it on a Sales Fact Table that has many duplicates. In fact, I use it on all of my models so that other users know how new the data is.

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      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] )
)
      • 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.