cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

 

 

LastTransaction.JPG

 

 

~heathernicoale
4 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Find Latest Transaction Date for Each Customer

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.

 

Super User
Super User

Re: Find Latest Transaction Date for Each Customer

@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] )
)
Super User
Super User

Re: Find Latest Transaction Date for Each Customer

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/

 

 

 

Super User
Super User

Re: Find Latest Transaction Date for Each Customer in Quickbooks

@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] )
)
34 REPLIES 34
Highlighted
Super User
Super User

Re: Find Latest Transaction Date for Each Customer

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.

 

Re: Find Latest Transaction Date for Each Customer

@kcantor I was afraid I was over complicating it. Smiley Happy 

 

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! Smiley Happy 

THANK YOU!!!

~heathernicoale

Re: Find Latest Transaction Date for Each Customer

@kcantor I was afraid I was over complicating it. Smiley Happy 

 

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! Smiley Happy 

THANK YOU!!!

~heathernicoale
Super User
Super User

Re: Find Latest Transaction Date for Each Customer

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

Re: Find Latest Transaction Date for Each Customer

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.

Moderator Eric_Zhang
Moderator

Re: Find Latest Transaction Date for Each Customer

@heathernicole

Using RANKX could also help.  Then you can find out the latest transactions(TransactionOrderByDateDesc=1) for each customer.

TransactionOrderByDateDesc = RANKX(FILTER(Table3,Table3[Customer]=EARLIER(Table3[Customer])),Table3[Date],,DESC,Skip)

捕获.PNG

 

If you have any question, feel free to let me know.

Re: Find Latest Transaction Date for Each Customer

@kcantor - Hello! Hope you had a good weekend!

 

I tried LastDate on the Transaction Date Modified - and this is the error I got

 

A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.The current operation was cancelled because another operation in the transaction failed. 

If I use this formula on the Calendar Table - no problem

 

~heathernicoale
Super User
Super User

Re: Find Latest Transaction Date for Each Customer

That is odd. I use it on a Sales Fact Table that has many duplicates. In fact, I use it on all of my models so that other users know how new the data is.

Super User
Super User

Re: Find Latest Transaction Date for Each Customer

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