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.

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

Hi, vpsoini 

Could you please consider sharing some dummy data and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

  1. Sample (dummy dataset) data as text, use the table tool in the editing bar
  2. Expected output from sample data
  3. Explanation in words of how to get from 1. to 2.

 

 

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

    Hi v-angzheng-msft 

    Let's simplify it into 2 tables only.

    Items:

     

    ItemNoDescriptionInventory
    1Item 110
    2Item 220
    3Item 330

     

    and ItemLedger:


    ItemNoQtyDate
    1-110.1.2022
    1211.1.2022
    1-312.1.2022
    2-210.1.2022
    2711.1.2022
    2-612.1.2022
    3-310.1.2022
    3211.1.2022
    3-512.1.2022

     

    and the expected outcome (in matrix visual) would be

     

     

    So the "value" in matrix visual should be the RunningTotal starting from inventory value  (10 for Item1, 20 for Item2 and 30 for Item3) and then modifying the inventory based on changes in ItemLedger, per item per date.

    The linkage between those 2 tables is based on "ItemNo"

     

     

    How this can be achieved?

    • v-angzheng-msft's avatar
      v-angzheng-msft
      Community Support
      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.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      There should also be a Date column in the Items table.