Calculated column - two values, only return the last value

[Tob_P]

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

[Anonymous · Accepted 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.

[Tob_P]

@AnonymousPerfect - works a treat for me so thank you so much.

[powerbiexpert22]

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]

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

[powerbiexpert22]

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?

[Tob_P]

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

[powerbiexpert22]

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

[Tob_P]

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

Hi @Tob_P ,

latestcust=

CALCULATE(MAX(Sales[Cust No]),FILTER(Sales,Sales[Posting Date]=MAX(Sales[Posting Date])))