Forum Discussion

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
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.
W42_Running total from multiple sources , starting from ground value.pbix46 KB

vpsoini

Helper I

4 years ago

Hi v-angzheng-msft

Let's simplify it into 2 tables only.

Items:

ItemNo	Description	Inventory
1	Item 1	10
2	Item 2	20
3	Item 3	30

and ItemLedger:

ItemNo	Qty	Date
1	-1	10.1.2022
1	2	11.1.2022
1	-3	12.1.2022
2	-2	10.1.2022
2	7	11.1.2022
2	-6	12.1.2022
3	-3	10.1.2022
3	2	11.1.2022
3	-5	12.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"