Forum Discussion

CoreyP's avatar
CoreyP
Solution Sage
6 years ago

A seemingly complex task...

Hey all,   I've got what might be a fun one for all you experts. Here's what I'm trying to do:   If any of you have a background in warehouse management, you might get this right off the bat. I'm...
Need Help
CoreyP's avatar
CoreyP
Solution Sage
6 years ago

I created a "Pallet Ranking" by going into query editor, and sorting first by SKU, then LOT, then FIFO date, and adding an Index column. 

 

Seems to work. So how would I create the running total measure? I tried the following but it did not work:

 

Cumulative Qty =
CALCULATE (
SUM( 'Inventory'[ONHAND_QTY] ),
FILTER (
ALL ( 'Inventory'[Pallet Rank] ),
'Inventory'[Pallet Rank] <= MAX ( ( 'Inventory'[Pallet Rank] ) )
)
)
kentyler's avatar
kentyler
Solution Sage
6 years ago

 

I think you want to store the current value before you do calculate
 
Cumulative Qty =
var  cur_index = max('inventory'[Pallet rank])

 

 
CALCULATE (
SUM( 'Inventory'[ONHAND_QTY] ),
FILTER (
ALL ( 'Inventory'[Pallet Rank] ),
'Inventory'[Pallet Rank] <= cur_index )
)
)
  • CoreyP's avatar
    CoreyP
    Solution Sage
    6 years ago

    That doesn't seem to be working. Just returns 1 for every value. Do we have to take item_no into context somehow?

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    If you just return the current index, does it show the correct number ?

  • CoreyP's avatar
    CoreyP
    Solution Sage
    6 years ago

    This is what it returns:

    Using this measure:

    Cumulative Qty =
    VAR cur_index = MAX('Inventory'[Pallet Rank])
     
    RETURN
    CALCULATE (
    SUM( 'Inventory'[ONHAND_QTY] ),
    FILTER (
    ALL('Inventory'[Pallet Rank]),
    'AIRMInventory'[Pallet Rank] <= cur_index ))
  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    Try moving the ALL() outside the FILTER()

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    and you can short cut the filter statement 

    FILTER (
    ALL('Inventory'[Pallet Rank]),
    'AIRMInventory'[Pallet Rank] <= cur_index ))
    by just writing
    , 'AIRMInventory'[Pallet Rank] <= cur_index
    as an argument to CALCULATE
    CALCULATE will turn it into a filter automatically
  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    I know the feeling. It can't seem impossible. There is just some little detail we have to find.

    If you just write the Rankx measure (following his example) in a measure and put it in a table, what does it return ?