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

Sean - I think that last solution may have worked! :) 

 

I'm getting with the Sales Manager to double check the data, but from the looks of it - it looks correct!! :D 

 

THANKS!!!!

 

EDIT. :) It's correct!!!  Sean

 

THANK YOU SO MUCH! Now- I understand what I did, but I'm not exactly sure WHY it worked. Did separating them take care of some underlying filter? That's what I had originally perceived to be the problem is because of the structure it was causing some filtering underneath that I couldn't see right away.

 

Thoughts?

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

    heathernicole Great! Glad you got it working!

     

    As far as why it was not working - I don't know - your guess is as good as mine?

     

    I really thought everything suggested in this post should have worked (including the Query Editor Group By)

     

    I personally use the Measure I suggested - and my setup is prettry much same as yours... so go figure...