Find Latest Transaction Date for Each Customer

[heathernicole]

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 short each time in accomplishing the real goal: The Sales info and the Customer are two separate tables - but have a relationship. 

This is the most recent attempt in DAX: 

Last Transaction = Filter(ALL(Customer[Customer Name]), Sales Detail[SalesTxnTimeModifed] = MAX(Sales Details[SalesTxnTimeModifed]))

But it gives this error: 'A table of multiple values was supplied where a single value was expected'

All I'm trying to do is generate a list of customers with their last transaction. From there I will look at a Rolling 11 months and a Rolling 13 months. When a customer goes 11 months without any activity - it will show in the report. And then when they go 13 months without any activty it will show in the 13 month report. But I have to have that list first.

Here's an example of the data I'm looking at:

[kcantor · Accepted solution]

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 · Accepted solution]

@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 · Accepted solution]

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 · Accepted solution]

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

@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!! 😄 

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]

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

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

[arify]

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

[heathernicole]

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

[arify]

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)

[arify]

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

[Vvelarde]

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

[heathernicole]

@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 · Accepted solution]

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/

[heathernicole]

@kcantor I was afraid I was over complicating it. 🙂 

We've had to logout for the day for IT to work in the database so I won't be able to give this a go until Tuesday (unless I can log back in sooner). 

I'll let you know! 🙂 

THANK YOU!!!

[kcantor]

I hope it works, @heathernicole, sometimes the easiest solution works. Other times you end up creating a new table and writing a novel lenght calculation.

Enjoy your long weekend!

[arify]

You'll probably get better performance if you do a Group By operation in the Query Editor when you're getting data. (In Query Editor right click Customer Name column, then in Operations pick Max, then in the column selector you can pick Transaction Date column). Depending on your datasource, this might download a lot less information from your datasource.

[heathernicole]

@kcantor I was afraid I was over complicating it. 🙂 

We've had to logout for the day for IT to work in the database so I won't be able to give this a go until Tuesday (unless I can log back in sooner). 

I'll let you know! 🙂 

THANK YOU!!!