DAX - help with last date based on multiple distinct keys

[deng2431]

Hi PowerBi community,

I am new to Powerbi and DAX, I am trying to solve an issue of selecting the MAX/Last date from a column (payment date) where there were multiple same numbers (Invoice Key column). if there is no multiple keys then just display the 1 payment date. i get the concept of what needs to be done but i just cant get my head wrapped around how to partition the distinct invoice keys and then use that to find the max/last date from that partition. any help or advice is really appreciated. thank you!!

 

[deng2431 · Accepted solution]

Hi Magnus,

Thank you for your response, i just so happen to came across a solution to my issues earlier using the below query:

Payment date (Final) = calculate(max(Payments[Payment date]),  allexcept(Payments, 'Payments[Invoice_key]))

which works exactly what i wanted, i have tried your solution as well but it doesnt take into consideration of finding the max date in the same set of Invoice keys.

thank for your time with this 🙂

[magsod]

You can create the following new measure and add it to your visual.

Latest payment =

CALCULATE(

    MAX(Payments[Payment date]),

    REMOVEFILTERS(Payments[Payment date])

)

The filter condition will allow finding the maximum payment date but keeping the Invoice_key to be the same.

Br,

Magnus