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 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

Sean - hey! :) 

 

Gave the Calculate solution a try - got a similar error:

 

A date column containing duplicate dates was specified in the call to function 'LASTDATE': this is not supported.

This doesn't make sense. They're transactions. One of our companies does several thousand transactions per day. They are going to be duplicate values but they're not the same transaction / customer. 

 

Not sure what the deal is. Trying a couple of different things now. 

 

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