topic Re: Calculated column - two values, only return the last value in DAX Commands and Tips

Calculated column - two values, only return the last value

Tob_P — Tue, 10 Dec 2024 13:27:04 GMT

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

Re: Calculated column - two values, only return the last value

powerbiexpert22 — Tue, 10 Dec 2024 14:56:31 GMT

Hi Tob_P ,

latestcust=

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

Re: Calculated column - two values, only return the last value

Tob_P — Tue, 10 Dec 2024 15:03:46 GMT

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

Re: Calculated column - two values, only return the last value

powerbiexpert22 — Tue, 10 Dec 2024 16:34:41 GMT

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?

Re: Calculated column - two values, only return the last value

Tob_P — Wed, 11 Dec 2024 09:27:38 GMT

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

Re: Calculated column - two values, only return the last value

powerbiexpert22 — Wed, 11 Dec 2024 10:54:01 GMT

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

Re: Calculated column - two values, only return the last value

Tob_P — Wed, 11 Dec 2024 10:58:50 GMT

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

Re: Calculated column - two values, only return the last value

powerbiexpert22 — Wed, 11 Dec 2024 16:35:43 GMT

Hi Tob_P ,

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

Re: Calculated column - two values, only return the last value

Anonymous — Fri, 13 Dec 2024 02:37:17 GMT

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.

Re: Calculated column - two values, only return the last value

Tob_P — Fri, 13 Dec 2024 10:00:24 GMT

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