Forum Discussion

SharonCNE's avatar
SharonCNE
Frequent Visitor
6 years ago

Inventory at multiple locations and multiple dates

Hi everyone.  I have a list of inventory from multiple locations that was taken on different dates.  I need to know the current total inventory for each item using the last date posted from each location.  Here is an example of my data

 

DateLocationItemQuantity
3/31/2020Location AItem 40
3/31/2020Location AItem 1156
3/31/2020Location AItem 20
3/31/2020Location AItem 30
4/10/2020Location AItem 40
4/10/2020Location AItem 10
4/10/2020Location AItem 2723
4/10/2020Location AItem 3377
4/14/2020Location BItem 492
4/14/2020Location CItem 459
4/14/2020Location EItem 412
4/14/2020Location BItem 11
4/14/2020Location CItem 10
4/14/2020Location EItem 16
4/14/2020Location BItem 219
4/14/2020Location CItem 214
4/14/2020Location EItem 26
4/14/2020Location BItem 33
4/14/2020Location CItem 37
4/14/2020Location EItem 312
4/15/2020Location EItem 46
4/15/2020Location DItem 41
4/15/2020Location FItem 443
4/15/2020Location GItem 416
4/15/2020Location EItem 10
4/15/2020Location DItem 10
4/15/2020Location FItem 10
4/15/2020Location GItem 11
4/15/2020Location EItem 26
4/15/2020Location DItem 223
4/15/2020Location FItem 212
4/15/2020Location GItem 251
4/15/2020Location EItem 36
4/15/2020Location DItem 30
4/15/2020Location FItem 3110
4/15/2020Location GItem 310
4/16/2020Location AItem 40
4/16/2020Location AItem 1452
4/16/2020Location AItem 2611
4/16/2020Location AItem 31525
4/17/2020Location AItem 40
4/17/2020Location AItem 1452
4/17/2020Location AItem 2481
4/17/2020Location AItem 31475

9 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi SharonCNE ,

     

    Try those measures:

     

    _LastDate = CALCULATE(MAX('Table'[Date]); ALLEXCEPT('Table';'Table'[Item]))
     
    Measure = CALCULATE(SUM('Table'[Quantity]); FILTER(ALLEXCEPT('Table'; 'Table'[Item]); 'Table'[Date] = [_LastDate]))
     
     
    Did I answer your question? Mark my post as a solution!
    Ricardo
    • SharonCNE's avatar
      SharonCNE
      Frequent Visitor
      6 years ago

      Ricardo thanks for answering so quickly - im getting this error

       

      The function SUM cannot work with values of type String

      • camargos88's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        6 years ago

        SharonCNE ,

         

        Be sure the quantity column is numeric.

         

        Did I answer your question? Mark my post as a solution!
        Ricardo