Forum Discussion

vpsoini's avatar
vpsoini
Helper I
4 years ago
Solved

Running total from multiple sources , starting from ground value?

Hello, and happy 2022!    My goal is to get inventory forecast, so I'm trying to calculate running total from multiple sources (sales and purchases) , but not really nailing it...  sales and purcha...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, vpsoini 

    ItemNo 2 should have a remaining amount of 19 on January 12.

     

    Try to create a measure like this:

    Measure = 
SUM(Items[Inventory])+
CALCULATE(
    SUM(ItemLedger[Qty]),
    FILTER(ALL(ItemLedger),'ItemLedger'[ItemNo]=MAX('Items'[ItemNo])&&'ItemLedger'[Date]<=MAX('ItemLedger'[Date])))

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vpsoini's avatar
vpsoini
Helper I
4 years ago

Also the problem with that running total is to show numbers correctly in matrix visual:  I have the running total number (starting from zero) in the lower matrix, and same measure, added by ground value (here 6) in the upper one.  Next to them is a small excel showing the changes over dates

 

As you can see, the lower matrix shows the change right, but all number are ground value (6) too low. Upeer one shows values right on those dates, the value actually changes, but not in between. As comparison, the graph visual shows the amount correct.

 

there is a same measure in both upper matrix and the graph, but still the matrix is NOT showing vaue to be "1" between 7.1.2022 and 14.1.2022 , but only shows the correct value on 7.1.22 and then the change value between 7.-14.1. (-5) instead?  Why is that?