Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Solved! Go to 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.
Proud to be a Super User!
@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] ) )
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 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] ) )
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)
If you have any question, feel free to let me know.
How can I rank the dates in ascending order?
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.
Proud to be a Super User!
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
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.
Proud to be a Super User!
@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] ) )
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.
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! 🙂
@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?
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.
Maybe using this:
Calculated Column
CustomerLastDateTransaction = CALCULATE(LASTDATE('Transaction'[TransactionDate]);ALLEXCEPT('Transaction';'Transaction'[CustomerName]))
Can share us the data model:
How many tables using for this and his relationship.
Customers
Transactions
anything else?
Tanks
Raw Data to work with if anyone wants it. I couldn't show customer Names though so I just did a count.
Transaction Age | Customer Last Transaction | Count of Customer Name | Customer Status |
330 | 7/7/2015 0:00 | 3919 | 11-Month Customer |
882 | 1/1/2014 0:00 | 3919 | 13+ - Month Customer |
877 | 1/6/2014 0:00 | 3919 | 13+ - Month Customer |
876 | 1/7/2014 0:00 | 3919 | 13+ - Month Customer |
875 | 1/8/2014 0:00 | 3919 | 13+ - Month Customer |
874 | 1/9/2014 0:00 | 3919 | 13+ - Month Customer |
873 | 1/10/2014 0:00 | 3919 | 13+ - Month Customer |
870 | 1/13/2014 0:00 | 3919 | 13+ - Month Customer |
869 | 1/14/2014 0:00 | 3919 | 13+ - Month Customer |
868 | 1/15/2014 0:00 | 3919 | 13+ - Month Customer |
867 | 1/16/2014 0:00 | 3919 | 13+ - Month Customer |
866 | 1/17/2014 0:00 | 3919 | 13+ - Month Customer |
863 | 1/20/2014 0:00 | 3919 | 13+ - Month Customer |
862 | 1/21/2014 0:00 | 3919 | 13+ - Month Customer |
861 | 1/22/2014 0:00 | 3919 | 13+ - Month Customer |
860 | 1/23/2014 0:00 | 3919 | 13+ - Month Customer |
859 | 1/24/2014 0:00 | 3919 | 13+ - Month Customer |
856 | 1/27/2014 0:00 | 3919 | 13+ - Month Customer |
855 | 1/28/2014 0:00 | 3919 | 13+ - Month Customer |
854 | 1/29/2014 0:00 | 3919 | 13+ - Month Customer |
853 | 1/30/2014 0:00 | 3919 | 13+ - Month Customer |
852 | 1/31/2014 0:00 | 3919 | 13+ - Month Customer |
849 | 2/3/2014 0:00 | 3919 | 13+ - Month Customer |
848 | 2/4/2014 0:00 | 3919 | 13+ - Month Customer |
847 | 2/5/2014 0:00 | 3919 | 13+ - Month Customer |
846 | 2/6/2014 0:00 | 3919 | 13+ - Month Customer |
845 | 2/7/2014 0:00 | 3919 | 13+ - Month Customer |
842 | 2/10/2014 0:00 | 3919 | 13+ - Month Customer |
841 | 2/11/2014 0:00 | 3919 | 13+ - Month Customer |
@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?
@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.
@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 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. 😕
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
64 | |
55 |