latest product for on a given transaction

[ConstMoss]

Hello everyone - i am trying to figure out how to get the last/latest product for a given transaction based on the line number as shown below. any idea how to do this?

[Sean · Accepted solution]

@ConstMoss

This seems to work!

Column =
CALCULATE (
    FIRSTNONBLANK ( 'Table'[ProductName], 1 ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[TransactionNum] ),
        'Table'[LineNum]
            = CALCULATE (
                MAX ( 'Table'[LineNum] ),
                ALLEXCEPT ( 'Table', 'Table'[TransactionNum] )
            )
    )
)

[Ashish_Mathur · Accepted solution]

Hi @ConstMoss,

Try this calculated column formula

=LOOKUPVALUE([ProductName],[LineNum],CALCULATE(MAX([LineNum]),FILTER(Data,[TransactionNum]=EARLIER([TransactionNum]))),[TransactionNum],[TransactionNum])

Hope this helps.

[Sean · Accepted solution]

@ConstMoss

This seems to work!

Column =
CALCULATE (
    FIRSTNONBLANK ( 'Table'[ProductName], 1 ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[TransactionNum] ),
        'Table'[LineNum]
            = CALCULATE (
                MAX ( 'Table'[LineNum] ),
                ALLEXCEPT ( 'Table', 'Table'[TransactionNum] )
            )
    )
)