Forum Discussion

Luxsta85's avatar
Luxsta85
New Member
2 years ago
Solved

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 ...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    Luxsta85 

    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
Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity 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's avatar
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's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Luxsta85 

    could you please share the file to i can take a look ? 

    because from my side, it works fine , 

     

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Luxsta85 

     

     

    measure 1 

    Expected Del = 

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

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

return res

     

     

     

    measur 2 : 

    Expected Del = 

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

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

return res

     

     

     

     

     

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

    • Luxsta85's avatar
      Luxsta85
      New Member
      2 years ago

      Hi Daniel29195,

       

      Measure 2 seems to be the same as meaure 1

      • Daniel29195's avatar
        Daniel29195
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Luxsta85 

        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