Forum Discussion

jrpoli2000's avatar
jrpoli2000
Frequent Visitor
8 years ago
Solved

Latest Dates

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 REFERENCEINVOICE IDTRANSACTION DATETRANSACTION STATUS
7011881620510157409/04/2017CURRENT
7011881620510138108/04/2017PREVIOUS
7011881620510138407/04/2017PREVIOUS

 

Many thanks in advance for all the help.

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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