Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vpsoini
Helper I
Helper I

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 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. 
1 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:

vangzhengmsft_0-1641779597622.png

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.

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

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.

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

 

vpsoini_0-1641547997772.png

 

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"

 

vpsoini_1-1641548238198.png

 

How this can be achieved?

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

vangzhengmsft_0-1641779597622.png

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
Helper I
Helper I

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_0-1641301269494.png

 

vpsoini
Helper I
Helper I

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
Super User
Super User

@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 ))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.