cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrpoli2000
Frequent Visitor

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.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks a lot!!!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors