Last Date Supplied DAX Help Please

[MVenables]

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. 

[mark_endicott]

@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

[MVenables]

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

[mark_endicott · Accepted solution]

@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.

[MVenables]

@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.