topic Re: DAX - help with last date based on multiple distinct keys in DAX Commands and Tips

DAX - help with last date based on multiple distinct keys

deng2431 — Mon, 07 Nov 2022 10:05:02 GMT

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!!

Re: DAX - help with last date based on multiple distinct keys

Anonymous — Mon, 07 Nov 2022 10:46:43 GMT

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

Re: DAX - help with last date based on multiple distinct keys

deng2431 — Mon, 07 Nov 2022 10:58:26 GMT

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 🙂