Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I would like to created a calculated column to return the last value in the Cust No column based on the lastest posting date. So in the example you can access through the link, the new column would return only HUW227.
Could anyone help with this?
https://drive.google.com/file/d/1j3QxtftIiuOLfmAecxKMmnjXUisw4DBo/view?usp=sharing
Solved! Go to Solution.
Hi @Tob_P
Thank you very much powerbiexpert22 for your prompt reply.
Try this:
LastCustNo =
VAR LatestDate =
CALCULATE(
MAX('Sales'[Posting Date]),
ALLEXCEPT('Sales', 'Sales'[Reporting Cust No])
)
RETURN
CALCULATE(
MAX('Sales'[Cust No]),
FILTER(
'Sales',
'Sales'[Posting Date] = LatestDate &&
'Sales'[Reporting Cust No] = EARLIER('Sales'[Reporting Cust No])
)
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Tob_P
Thank you very much powerbiexpert22 for your prompt reply.
Try this:
LastCustNo =
VAR LatestDate =
CALCULATE(
MAX('Sales'[Posting Date]),
ALLEXCEPT('Sales', 'Sales'[Reporting Cust No])
)
RETURN
CALCULATE(
MAX('Sales'[Cust No]),
FILTER(
'Sales',
'Sales'[Posting Date] = LatestDate &&
'Sales'[Reporting Cust No] = EARLIER('Sales'[Reporting Cust No])
)
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@AnonymousPerfect - works a treat for me so thank you so much.
Hi @Tob_P ,
I am not sure what you are looking sorry, you can try to utilize SUMMARIZE () in DAX based on your need
Hi @powerbiexpert22
Thanks for the response - doesn't quite get me what I am looking for unfortunately. It returns the last Cust No listed in the table based on the posting date eg. GEO02 is the last Cust No as there on sales on 10/12/2024 therefore the calculated column returns GEO02. I probanly should have stated that there will be hundreds of Cust Nos
Hi @Tob_P ,
I do not see customer GEO02 in your dataset, can you please share what is the expected output with some sample data?
Hi @powerbiexpert22
I've updated the file with a bit more dummy data. I only included 1 example Cust No when I should have added more. In this case, because Customer No HUW227 is the latest posting date (05/12/2024), then that value is returned in the column. My expected output would be...
For ABC01 Cust No, return HUW227, for GEO02 Cust No, return HUW221
Hi @Tob_P
What is the relationship or business rule between ABC01 and HUW227
Similary, What is the relationship or business rule between GEO02 and HUW221
@powerbiexpert22
A Cust No can change. To maintain reporting on a Customer, a Reporting Cust No is created to retain the original Cust No. For the purposes of this task, I need to be able to output the last Cust No based on the most recent Posting Date
Hi @Tob_P ,
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |