Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create measure to identify most recent data (without date functions)

Goal: create a measure to identify the most recent data for a specific product ID in a specific month.

Issue: there is no date information, but rather a VersionID. The highest VersionID will indicate the most recent data.

Example: for the Car product with ProductID "1010," I want to be able to return the most recent data for the month of January (i.e. 675).

  • Anonymous's avatar
    Anonymous
    6 years ago

    This might work. You could probably make your ProductID and ProductGroup dynamic as well.

     

    Add a measure to pull out the max of VersionID

    MaxID = MAX('YourTable'[VersionID])

    Add a "helper" column to identify max VersionID

    MaxCheck = IF('YourTable'[VersionID] = [MaxID],1,0)

    Then you can use your helper column to filter the table to the max of VersionID as well as your other criteria

    measure =
CALCULATE (
    MAX ( 'YourTable'[Units] ),
    FILTER (
        'YourTable',
        'YourTable'[ProductGroup] = "Car"
            && 'YourTable'[ProductID] = 1010
            && 'YourTable'[MaxCheck] = 1
    )
)

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This might work. You could probably make your ProductID and ProductGroup dynamic as well.

     

    Add a measure to pull out the max of VersionID

    MaxID = MAX('YourTable'[VersionID])

    Add a "helper" column to identify max VersionID

    MaxCheck = IF('YourTable'[VersionID] = [MaxID],1,0)

    Then you can use your helper column to filter the table to the max of VersionID as well as your other criteria

    measure =
CALCULATE (
    MAX ( 'YourTable'[Units] ),
    FILTER (
        'YourTable',
        'YourTable'[ProductGroup] = "Car"
            && 'YourTable'[ProductID] = 1010
            && 'YourTable'[MaxCheck] = 1
    )
)