The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All -
I have scoured the web looking for an answer on this topic and while there are lots of tutorials/solutions for finding last/max date by customerID (for a purchase, visit, survey taken, etc.), I can't find anything that goes one step further -- and looks at clientID and the last/max date associated with that client ID, then returns the name of that last item purchased or name of the survey they took.
So, for example -- sample data:
ClientID PurchaseDate ItemPurchased
123 1/1/2021 Shirt
123 1/5/2021 Pants
456 1/11/2021 Shoes
456 1/25/2021 Book
I'm looking to turn the above data into a table that shows the client's last/max/latest purchase date and the item purchased associated with that last date - so, the output would be:
Client Last Purchase Date ItemPurchased
123 1/5/2021 Pants
456 1/25/2021 Book
Finding the last/max date either via a calc column or measure is no problem, but any help as far as retrieving the actual item purchased associated with that date would be so helpful.
thank you!
Solved! Go to Solution.
@samdep , use this measure for ItemPurchased, if needed take max of date
Measure =
VAR __id = MAX ('Table'[ClientID] )
VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )
Hi,
This formula is exactly what I need however it keeps telling me the syntax for CALCULATE is incorrect.
Any ideas how to overcome this?
Hi,
I also have an similar issue but keep getting an error with the calculate. Did you manage to find a solution for the calculate error? I don't think anyone replied back for a full response to the calculate error in the below measure....
Did you finally get a solution? Could you share if possible?
Thanks in advanced.
Measure =
VAR __id = MAX ('Table'[ClientID] )
VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )
Hi,
You could try this:
Measure =
VAR __id = MAX('Table'[ClientID])
VAR __date =
CALCULATE(
MAX('Table'[PurchaseDate]),
ALLSELECTED('Table'),
'Table'[ClientID] = __id
)
RETURN
CALCULATE(
MAX('Table'[ItemPurchased]),
'Table'[ClientID] = __id,
'Table'[PurchaseDate] = __date
)
Can you share your code, plus some sample data?
Hi samdep,
My sample data is:
Client ID | Purchase Date | ProductName ID |
4001 | 21/07/2022 | Pdt ID-199 |
4001 | 20/07/2022 | Pdt ID-200 |
4001 | 19/07/2022 | Pdt ID -202 |
4001 | 18/07/2022 | Pdt ID -201 |
Output:
Client ID | Purchase Date | ProductName ID |
4001 | 21/07/2022 | Pdt ID-199 |
Code is:
Measure =
VAR __id = MAX ('Table'[ClientID] )
VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
CALCULATE ( max ('Table'['ProductName ID] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )
Appreciate your assistance.
@samdep , use this measure for ItemPurchased, if needed take max of date
Measure =
VAR __id = MAX ('Table'[ClientID] )
VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )
Hi,
The measure
Measure =
VAR __id = MAX ('Table'[ClientID] )
VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )
Always shows an error with the calculate - is there something wrong with the above measure where the last calculate shows an error.
Thanks in advanced.
@amitchandak - thank you so much! I have been trying to figure this out for a while now to use within multiple dashboards - and your suggested measure worked perfectly. thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
16 | |
15 |
User | Count |
---|---|
79 | |
32 | |
30 | |
24 | |
21 |