Forum Discussion

Rickard's avatar
Rickard
Frequent Visitor
10 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 fo...
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 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
    10 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?