Showing the Recent Quantity

Hi,

I'm having an issue showing the Quantity of stock due in on the Date it is due in. For example, we have a product 36965 which has 510 pieces due in on 14/03/24. However, on the Desktop board i have created it seems to combine quantities from 2 seperate dates and then displays them I need it to show the incoming quantity for the latest delivery date.

my bad

Expected Qty = 

var ds =  filter( 
    SUMMARIZE(
    Purchase_Order_Line_Excel,
     Purchase_Order_Line_Excel[ExpDate],
     Purchase_Order_Line_Excel[Exp Date],
     Purchase_Order_Line_Excel[Quantity]
    )
     ,  Purchase_Order_Line_Excel[exp date] <> "Delivered" ) 

var res = 
SELECTCOLUMNS(
index(
    1,
    ds,
    ORDERBY(Purchase_Order_Line_Excel[ExpDate],ASC)
),Purchase_Order_Line_Excel[Quantity])

return res

7 Replies

Daniel29195
Community Champion
2 years ago
Luxsta85
NB : i notice that your dates are in datatype general --> convert the datatype to date .
try the following measure :

measure 1 :
measure 1 = var ds = filter( Table3 , Table3[exp date] <> "Delivered" ) var res = firstnonblank(ds , Table3[ExpDate]) return SELECTCOLUMNS(res,Table3[ExpDate])

let me know if this helps.

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

measure 2
measure 2 = var ds = filter( Table3 , Table3[exp date] <> "Delivered" ) var res = firstnonblank(ds , Table3[ExpDate]) return SELECTCOLUMNS(res,Table3[Quantity])
- Luxsta85
  New Member
  2 years ago
  Hi Daniel29195
  
  Thanks for you response!
  
  That nearly worked, seems to be skippping the March date and showing May, can't figure out why.
  - Daniel29195
    Community Champion
    2 years ago
    Luxsta85
    could you please share the file to i can take a look ?
    because from my side, it works fine ,
Luxsta85
New Member
2 years ago
Absolute legend! That all worked a treat. Thank you very much!