Most Recent Transaction Per Store

[win_anthony]

The goal is to flag only the most recent transaction per store. My current output will flag the most recent transaction for each unique transaction. The requirements changed and I need to flag only the most recent transaction but can't figure out to only get the most recent transaction regardless of any other transactions. For context, below is: current measure / sample data table = current output / sample data = expected output. 

Any advice on how I can flag only the most recent transaction per store? 

Current Measure

Latest Transaction = 
IF(
    Transactions[Date]
    = CALCULATE(
        MAX( Transactions[Date] ),
        ALLEXCEPT( Transactions, Transactions[Transaction_ID] )
    ), 1
)

Current Output = Sample Data Table

Transaction_ID	Store_Num	Status	Date	Latest_Transaction
101	5	New	1-Jan-21
101	5	In Process	2-Jan-21
101	5	Complete	3-Jan-21	1
102	5	New	1-Jan-21	1
103	5	New	12-Jan-21
103	5	In Process	28-Feb-21	1
104	5	New	25-Jan-21
104	5	In Process	26-Jan-21
104	5	Deleted	27-Jan-21	1

Expected Output = Sample Data Table

Transaction_ID	Store_Num	Status	Date	Latest_Transaction
103	5	In Process	28-Feb-21	1

Your advice is greatly appreciated. 

[smpa01]

@win_anthonycan you try this measure

Measure :=
IF (
    CALCULATE (
        MAX ( 'Table 1'[Date] ),
        FILTER (
            VALUES ( 'Table 1'[Date] ),
            'Table 1'[Date] = CALCULATE ( MAX ( 'Table 1'[Date] ), ALL ( 'Table 1' ) )
        )
    )
        <> BLANK (),
    "latest"
)

[win_anthony]

@smpa01  Thank you so much for your support! This is pretty much spot on for what I am looking for. Question for you though. If I wanted to do this same action but apply it to every store, how would you update this measure? My sample data only reflected 1 store. Let's assume that there are 5,000 stores and you wanted to reflect the most recent transaction for each store. How would you update this measure? The output when I dropped your suggestion into my model gave me the most recent transaction but only reflected 1 store when the goal is to reflect the most recent transaction for all stores. 

Ex: 5,000 stores = 5,000 most recent transactions output

[smpa01 · Accepted solution]

@win_anthony

Measure 2 = IF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            VALUES ( 'Table'[Date] ),
            'Table'[Date] = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[store]))
        )
    )
        <> BLANK (),
    "latest"
)

[MattAllington]

Do you have a dimensional model with a dimension table for stores? If not, you should create one. Read my article here https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

you need dimensions for customer, transaction id and date (minimum)

Assuming you have a dim model, the following measure should return the last transaction date into a table visual of store IDs coming from the dimension table. 

Last transaction date = max(trans[date])

Assuming there is only 1 transaction id per day (or the transaction IDs increment in value over time) the following will return the id as a measure. 

last transaction id = calculate(max(trans[id]),all(calendar[date]))

If you then add the transaction id from the dim table to the table containing the customer id, the following measure should force only those transactions to show.

Show Transaction = If(SELECTEDVALUE(trans[id])=[last transaction id],1)

i have not tested any of this as you have not provided any test data.