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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Tob_P
Helper V
Helper V

Calculated column - two values, only return the last value

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

vnuocmsft_0-1734057356541.png

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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.

 

vnuocmsft_0-1734057356541.png

 

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.

 

 

powerbiexpert22
Impactful Individual
Impactful Individual

Hi @Tob_P ,

I am not sure what you are looking sorry, you can try to utilize SUMMARIZE () in DAX based on your need

Tob_P
Helper V
Helper V

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

powerbiexpert22
Impactful Individual
Impactful Individual

Hi @Tob_P ,

latestcust=
CALCULATE(MAX(Sales[Cust No]),FILTER(Sales,Sales[Posting Date]=MAX(Sales[Posting Date])))
 
powerbiexpert22_2-1733842580322.png

 


 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.