Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heathernicole
Continued Contributor
Continued Contributor

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
kcantor
Community Champion
Community Champion

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Sean
Community Champion
Community Champion

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

View solution in original post

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/

 

 

 

View solution in original post

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

View solution in original post

35 REPLIES 35
Eric_Zhang
Employee
Employee

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

Anonymous
Not applicable

How can I rank the dates in ascending order? 

kcantor
Community Champion
Community Champion

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

What if I need to create a column with last transaction date for each customer? LASTDATE('Sales'[OrderDate]) will give only a single date for all customers. Am I right?

 

For Example:

Mathew - 12/03/ 2019

John - 09/ 07/ 2018

Raju - 08/ 08/ 2019  etc

 

I tried:

CALCULATE(LASTDATE(SDimProCltDept[Admission]), SDimProCltDept[ClientSK] = Clients[ClientSK])

 

but throwing an error

Last date worked for me! I've been looking for the solution for a few hours now and finally stumbled upon this. I added 

Last Date = LASTDATE(Visits_Data[Date])

 

to a "New Column" so that i could use this data in a "Stacked Column Sheet" to show the last visit date on a job.

 

Thanks again!

@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

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@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] )
)
heathernicole
Continued Contributor
Continued Contributor

@SeanHad to change SalesTxnTimeModifed to a Date data type. It was orginally a DateTime data type. 

 

I'm thnking that's what caused the issue. 🙂 

 

On my way to phase 2 of this report. THANK YOU!

 

@kcantor - I believe your solution was correct too - I think the DATETIME was messing it up.

~heathernicoale

@Sean @kcantor -

 

Well - I got the function to work - but it's not pull the proper data. The items grouped in red are the same customer. What it appears to be doing is pulling EACH transaction for the customer; NOT the LAST transaction. So I'm not sure what it's doing. 

 

I've tried all of the suggestions from yesterday. None of them give errors but all of them are producing the same results it seems. I've also tried switching out MAX for LASTDATE.

 

Customer Last TransactionCustomer Last Transaction

 

Any ideas or thoughts about what might be the problem? I'm not sure what to do. I've tried using the LAST transaction date, the LAST salesTxnTime created, Time modified - I'm thinking it has to do with how Quickbooks handles data but I'm not entirely sure. @arify @Eric_Zhang - any thoughts or input you have is greatly appreciated! 🙂 

~heathernicoale

@heathernicole Are you using this as a measure or as a calculated column? I have one application of this where I add it as a calculated column in my customer table. that way is pulls a single date per customer from the sales fact table to the customer table. Perhaps this method would work for you?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

~heathernicoale

Maybe using this:

 

Calculated Column

 

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

 

sol1.png




Lima - Peru

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

 

 

Customer Activity 2.JPG

~heathernicoale

Can share us the data model:

 

How many tables using for this and his relationship.

 

Customers

Transactions

anything else?

 

Tanks




Lima - Peru

@Vvelarde  Tables used in this report (Not able to show all columns)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.

 

Transaction AgeCustomer Last TransactionCount of Customer NameCustomer Status
3307/7/2015 0:00391911-Month Customer
8821/1/2014 0:00391913+ - Month Customer
8771/6/2014 0:00391913+ - Month Customer
8761/7/2014 0:00391913+ - Month Customer
8751/8/2014 0:00391913+ - Month Customer
8741/9/2014 0:00391913+ - Month Customer
8731/10/2014 0:00391913+ - Month Customer
8701/13/2014 0:00391913+ - Month Customer
8691/14/2014 0:00391913+ - Month Customer
8681/15/2014 0:00391913+ - Month Customer
8671/16/2014 0:00391913+ - Month Customer
8661/17/2014 0:00391913+ - Month Customer
8631/20/2014 0:00391913+ - Month Customer
8621/21/2014 0:00391913+ - Month Customer
8611/22/2014 0:00391913+ - Month Customer
8601/23/2014 0:00391913+ - Month Customer
8591/24/2014 0:00391913+ - Month Customer
8561/27/2014 0:00391913+ - Month Customer
8551/28/2014 0:00391913+ - Month Customer
8541/29/2014 0:00391913+ - Month Customer
8531/30/2014 0:00391913+ - Month Customer
8521/31/2014 0:00391913+ - Month Customer
8492/3/2014 0:00391913+ - Month Customer
8482/4/2014 0:00391913+ - Month Customer
8472/5/2014 0:00391913+ - Month Customer
8462/6/2014 0:00391913+ - Month Customer
8452/7/2014 0:00391913+ - Month Customer
8422/10/2014 0:00391913+ - Month Customer
8412/11/2014 0:00391913+ - Month Customer
    

 

~heathernicoale

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

 

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale

@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
Continued Contributor
Continued Contributor

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

~heathernicoale

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.