Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Last Date Supplied DAX Help Please

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's avatar
    mark_endicott
    1 year ago

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

4 Replies

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        mark_endicott
        Icon for Super User rankSuper User
        1 year ago

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