Forum Discussion

Rickard's avatar
Rickard
Frequent Visitor
9 years ago

Quantity on hand

Hi, 

 

I am trying to visualize quantity on hand, over time, based on transaction history. I am accumulating all historical transactions to get the quantity in stock. 

 

 

 

This formula works fine if there are a short period but when looking at several years I need to use filters. 

 

When using a yearly filter the first year of data falls out of the calculation and the quantity on hand gets negative.

 

 

Any ideas how to use filters and still capture all transactions?

 

Regards

Rickard Blomqvist

 

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Rickard

     

    In this scenario, if you want to calculate the cumulative quantity for all years, your formula is correct. And if the cumulative total at the beginning of a year is negative, you can't avoid displaying negative values when you select a year in slicer to filter data. For your requirement, if you always want to start with positive value in each year. You should use TOTALYTD() instead of using Sum with a cumulative dates filter.

     

    Regards,

    • Rickard's avatar
      Rickard
      Frequent Visitor
      9 years ago

      Hi,

       

      v-sihou-msft Thanks for your answear. TOTALYTD will only work for Profit & Loss, not for the Balance Sheet or for MRP purposes where the opening balance for the current year is the closing balance for the previous year. To view just the TOTALYTD transactions without the opening balance is not that useful. And to visualise 6 years data unfiltered is not that useful either.

       

      I guess what I am looking for is equal to TOTALYTD but for all years?