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 think you should stick to 'Sales Details' and 'Customer'

 

So you say they have a direct relationship based on Customer ID

'Sales Details' is on the MANY side * => 1 'Customer' on the 1 side of the relationship

 

Have you tried a table visualization with just these 2 items => Customer Name and Last Transaction (Measure)

 

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 Yes. It's a data module set up by QQube to work with Quickbooks data. 

 

I've tired JUST using those two items. It cut down on the amount of "Last Transactions" per customer but it still won't just pull the most recent and ONLY the most recent. :/

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

     

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

    Sean Just started working on this today - getting ready to try. :) Thanks for the suggestion!