Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago
Solved

Get Last Values from a Table

Hello.

From my CRM I am obtaining information that I must graph. I need to have for each customer the latest value for various attributes. Working with the information I finally have a table that contains accountid (the client), attribute (the various attributes), cretatedon (the date of change), newname (the value of that attribute) and Index (column added by me after sorting the changes by date.

I have tried to do it in various ways and I am not getting the data, could you help me?

Thanks a lot

  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Syndicate_Admin 

    Create a Calculated Column to Identify the Latest Record:

    LatestRecord =
    RANKX(
    FILTER(
    TableName,
    TableName[accountid] = EARLIER(TableName[accountid]) &&
    TableName[attribute] = EARLIER(TableName[attribute])
    ),
    TableName[createdon],
    ,
    DESC
    )

     

    Then, you can create a filtered table or measure to get only the latest records where the rank is 1.

    LatestValuesTable =
    FILTER(
    TableName,
    TableName[LatestRecord] = 1
    )

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Syndicate_Admin 

    Create a Calculated Column to Identify the Latest Record:

    LatestRecord =
    RANKX(
    FILTER(
    TableName,
    TableName[accountid] = EARLIER(TableName[accountid]) &&
    TableName[attribute] = EARLIER(TableName[attribute])
    ),
    TableName[createdon],
    ,
    DESC
    )

     

    Then, you can create a filtered table or measure to get only the latest records where the rank is 1.

    LatestValuesTable =
    FILTER(
    TableName,
    TableName[LatestRecord] = 1
    )