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...
  • 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.

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.