Find Latest Transaction Date for Each Customer

Maybe using this:

Calculated Column

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

Have you tried doing what I said to get latest date for each customer?

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

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?

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

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 - Hey! :) Sean I believe also suggested this or something similar.

However - I'm still getting the same results. The box in red is all the same customer. Also - the Cutomer, Transaction Date are in separate tables. I don't know if that makes a difference or not. 

Can share us the data model:

How many tables using for this and his relationship.

Customers

Transactions

anything else?

Tanks

Vvelarde  Tables used in this report (Not able to show all columns)

Raw Data to work with if anyone wants it. I couldn't show customer Names though so I just did a count.

heathernicole what is the relationship between these 3 tables?

First you were using 'Sales Details' and 'Customer'

Now it seems you are using "Calendar - Transaction Date' and 'Customer'

Where is the Last Date you are looking for in 'Sales Details' or 'Calendar - Transaction Date'?

And how are these 3 tables related?

Create the calculated column in Customers.

CustomerLastDateTransaction = CALCULATE(LASTDATE('Calendar-Transact'[TransactionDate]);ALLEXCEPT('Customer';'Customer'[CustomerAccount]))

Or

Created in Sales Details the calculated column should be:

CustomerLastDateTransaction = CALCULATE(LASTDATE('Calendar-Transact'[TransactionDate]);ALLEXCEPT('SalesDetails';'SalesDetails'[CustomerAccount]))

Please try and Feedback

Sean - I've tried a couple of different solutions.

The Sales Details has all transaction information - that was what I was originally using. When that wasn't producing the correct results I attempted the Calendar - Transaction Date Table which is linked to the Sales Details table.

Sales Details is linked to Customer table with a Customer ID Link and the Calendar Table.

The Calendar table is simply a lookup table. 

Information, such as the Customer Name are in the Customer Table - NOT the Sales Details table but they are related to each other. 

arify -Hey! :) Yes, I did try. But it wouldn't work because Customer and the Transaction Dates are in separate tables. Or at least I couldn't see a way to make it work. It didn't give me the option to select a different table in Group BY.

Doesn't Transaction Dates table have information of which customer made that transaction? If not, how did you create the tables in your screenshots? Did you join them?

You can merge those 2 tables in Query Editor (you can use the buttons)

@heathernicole Oh I see, they have a relationship. In that case, in the Query Editor, Customers table should have a Transaction Dates column created for this relation, and that column should have Tables or Records. You can expand that column (click the button on top right corner of the column, pick your date column in there). Then you should be able to do the Group By operation.

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 Just started working on this today - getting ready to try. :) Thanks for the suggestion!