Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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:
Solved! Go to 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.
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.
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:
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?
Hi,
There should also be a Date column in the Items table.
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.
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?
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?
@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 ))
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
86 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |