Forum Discussion

GoldenRet's avatar
GoldenRet
New Member
5 years ago

Inventory Sum by StoreID, ItemID

I have the following table:

 

ID, StoreID, ItemID, Date, Quantity, Value

1, 1, 1, 20200101, 10, 100

2, 1, 1, 20200228, 5, 50

3, 2, 1, 20200101, 2, 20

4, 1, 2, 20200301, 6, 60

 

I have also a Date slicer based on Date field.

 

I would like to get a result based on the last valid row Goup By StoreID, ItemID:

Slicer interval: 20200101->20200131

Result:

1, 1, 1, 20200101, 10, 100

3, 2, 1, 20200101, 2, 20

Total inventory = 120

 

Slicer interval: 20200101->20200331

Result:

1, 1, 1, 20200101, 10, 100

3, 2, 1, 20200101, 2, 20

4, 1, 2, 20200301, 6, 60

Total inventory = 180

 

Any help or hint will be highly appreciated.

 

Thanks

Need Help

5 Replies

  • gpiero's avatar
    gpiero
    Skilled Sharer
    5 years ago

    Hi GoldenRet,

     

    what do you mean when are you saying  "the last valid row"?

    What column contains  data that determine if the row is valid or not? 
    How we can exclude ID #3 if date belong to slicer interval?

    Regards

     

    • GoldenRet's avatar
      GoldenRet
      New Member
      5 years ago

      This solution did not solve my problem as Item 1 will be summed up more than one single time.

      I needed to show and sum only last occurrence of Item 1 having Date <= DateSilecerLimit.

      However I changed my approach and added a column "RowValue=" CurrentRowInventoryValue-PreviousRowInventoryValue.

      I used RowValue to get the correct sum of the Inventory value.

      Thanks anyway for your help.