Forum Discussion

Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
1 year ago
Solved

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

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

9 Replies

  • powerbiexpert22's avatar
    powerbiexpert22
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    Hi Tob_P ,

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

     


     

  • Tob_P's avatar
    Tob_P
    Icon for Helper V rankHelper V
    1 year ago

    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's avatar
      powerbiexpert22
      Icon for Impactful Individual rankImpactful Individual
      1 year ago

      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's avatar
        Tob_P
        Icon for Helper V rankHelper V
        1 year ago

        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's avatar
    powerbiexpert22
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    Hi Tob_P ,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Tob_P
      Icon for Helper V rankHelper V
      1 year ago

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