Running total from multiple sources , starting from ground value?

[vpsoini]

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 purchases are in different tables, which are linked with item number. Both of these are are also linked to same calendar table. One spice in this soup is also a base inventory value, so the counting should not start from zero. 

The form of these tables is (simplified):  Item number / quantity / date

and for both of them separately, I get the running total nicely using:

CALCULATE(SUM('Purchases'[Quantity]),FILTER(ALLSELECTED('Purchases'),'Calendar'[Date] <= MAX('Calendar'[Date])))

CALCULATE(SUM('Sales'[Quantity]),FILTER(ALLSELECTED('Sales'),'Calendar'[Date] <= MAX('Calendar'[Date])))

So both of those running totals are counting nicely cumulative numbers on dates of purchases/sales listed on the table. Bot how cal I sum these two together over time?  

First of all, sales should have negative impact and purchases positive impact on inventory in certain date. Secondly, the easy daily sum or these two running totals don't seem to make it, since sales and purchases do not necesserily happen at same dates.

So any help formulating inventory value over time , where the base value + changes so far and the base value for next change?

Thanks a lot for your help, really appreciated. 

[v-angzheng-msft]

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]

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"

How this can be achieved?

[Ashish_Mathur]

Hi,

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

[v-angzheng-msft · Accepted solution]

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]

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?

[vpsoini]

Hi @amitchandak 

How to get the "ground value" added?  The value is located in yet another table called items  (Items[Inventory]) and it is today's value, from where the plus/minus calculations should start.

It cannot be added to the measure, that it won't add it separately to each date, or how?

[amitchandak]

@vpsoini , This should use a calendar table

like

CALCULATE(SUM('Purchases'[Quantity]),FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))
CALCULATE(SUM('Sales'[Quantity]),FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))

and month, date, qtr, year table should always come from Calendar

Also, you might need all not allselected

CALCULATE(SUM('Purchases'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))
CALCULATE(SUM('Sales'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))

or like

Inventory =
Var _max = maxx(allselected('Calendar') , 'Calendar'[date])
return
CALCULATE(SUM('Purchases'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] <=_max )) -
CALCULATE(SUM('Sales'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] <=_max ))