topic Re: Last Date Supplied DAX Help Please in DAX Commands and Tips

Last Date Supplied DAX Help Please

MVenables — Thu, 21 Nov 2024 10:20:49 GMT

Hi all,

I have a measure in place called [PO QTY Sent] this just Sums PurchaseOrder[QtySent].

My goal with this dax i need is to be able to see the LASTDATESUPPLIED by using that and PurchaseOrder[ProcessDate]. In my screenshot below it contains all of these fields. But i want LastDateSupplied to show me the 22 October 2024 date as that was the last time PO QTY Sent was more than "0". This is so i can identify when we actually had stock delivered to us last.

I also want the condition when [PO QTY Sent] is more than 0 show as blank.

The below is the dax im using that currently shows me the incorrect dates as the 3 dates above i want them all to say 22/10/2024 which is in theory the last date it was [PO QTY Sent] greater than "0".

LastDateSupplied =

IF(

[PO QTY Sent] > 0,

BLANK(),

CALCULATE(

MAX(PurchaseOrder[ProcessDate]),

FILTER(

PurchaseOrder,

[PO QTY Sent] = 0)))

Any help will be great, i know using the MAX is causing this issue but need a work around.

Thanks for reading, hope you can help.

Re: Last Date Supplied DAX Help Please

mark_endicott — Thu, 21 Nov 2024 10:34:27 GMT

MVenables - Try this

IF( [PO QTY Sent] > 0, BLANK(), CALCULATE( MAX(PurchaseOrder[ProcessDate]), FILTER( ALL(PurchaseOrder[ProcessDate]), [PO QTY Sent] > 0)))

If this works, please accept as the solution to help others with the same issue

Re: Last Date Supplied DAX Help Please

MVenables — Thu, 21 Nov 2024 10:38:19 GMT

Hi Mark,

Its almost there its just showing me 22/10/2024 against every date now though as per below. Rather than using the original screenshot as an example. i would need it to show me 22/10/2024 on just the top 3 lines as [PO QTY Sent] is 0

Re: Last Date Supplied DAX Help Please

mark_endicott — Thu, 21 Nov 2024 10:43:47 GMT

MVenables - It's showing against Process Dates where the result is BLANK this is the same as 0. Notice it does not show on 22/10/2024 where the value is above 0.

You could apply a filter to this table to remove the BLANK rows from PO QTY Sent.

Re: Last Date Supplied DAX Help Please

MVenables — Thu, 21 Nov 2024 10:46:43 GMT

mark_endicott thanks Mark, im just going to remove the blank out of the dax, and use a visual filter to remove the blanks from the table.