Latest Dates

[jrpoli2000]

Hello,

Need a calculated column to flag the most recent transaction on the sample table below, desired output will be the last column(TRANSACTION STATUS).

Unfortunately I need to keep all the rows for reference so removing the duplicates based on PAYMENT REFERENCE column is not an option.

PAYMENT REFERENCE	INVOICE ID	TRANSACTION DATE	TRANSACTION STATUS
70118816205	101574	09/04/2017	CURRENT
70118816205	101381	08/04/2017	PREVIOUS
70118816205	101384	07/04/2017	PREVIOUS

Many thanks in advance for all the help.

[TomMartens · Accepted solution]

Hey,

to create a calculated column you can use this DAX statement

CALC Transaction Status = 
var currentDate = 'Table1'[TRANSACTION DATE]
return
CALCULATE(
    IF(MAX('Table1'[TRANSACTION DATE]) = currentDate, "CURRENT", "PREVIOUS")
    ,ALLEXCEPT('Table1',Table1[PAYMENT REFERENCE])
)

The underlying assumption is that there are no two or more dates for a Payment Reference with the same date value.

Hope this is what you are looking for

Regards

Tom

[jrpoli2000]

Thanks a lot!!!