Forum Discussion
Solved
Running Total based on two values from different tables.
I have data which looks like this: The first 4 columns are in a table named [orders] The last column is in a table named [on_hand] this table also has the [org] and [code] field so it can lin...
hi mgiusto
Please try this
Step1 : Created tables based on data shared by you, please note that I have kept all datatypes as whole number except date which is Date Datatype. I have not linked these two tables in data model.
Step2 :
Create this measure.
On Hand =VAR _SelOrg = SELECTEDVALUE(Orders[org])VAR _selCode = SELECTEDVALUE(Orders[code])VAR _SelDate = SELECTEDVALUE(Orders[Date])VAR _OnHandQty = SELECTCOLUMNS(FILTER(OnHand, OnHand[org] = _SelOrg && OnHand[code] = _selCode), "@OnHand", OnHand[on_hand])VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]), Orders[Date] <= _SelDate)RETURN _OnHandQty - _SumQty
RT =
var md = max('Table'[date])
return max('Table'[on_hand])-CALCULATE(sum('Table'[ord_qty]),'Table'[date]<=md)
You can adjust the formula to provide the on_hand lookup differently.
This does not work as it does not take into account that you need to match the [on_hand] records to the [ord_qty] records based on [org] and [code] being equal in both tables.
Here's what the two tables look like:
[orders]
| org | code | date | ord_qty |
| 202 | 50662 | 3/8/2024 | 3 |
| 202 | 50662 | 3/14/2024 | 10 |
| 202 | 50662 | 3/15/2024 | 1 |
| 202 | 50662 | 3/20/2024 | 2 |
| 202 | 29314 | 3/15/2024 | 1 |
| 202 | 29314 | 3/20/2024 | 1 |
| 202 | 74021 | 2/6/2024 | 3 |
| 202 | 74021 | 2/16/2024 | 24 |
| 202 | 74021 | 2/28/2024 | 4 |
| 202 | 74021 | 2/29/2024 | 1 |
| 202 | 74021 | 3/12/2024 | 13 |
| 202 | 74021 | 3/20/2024 | 2 |
| 202 | 11293 | 3/21/2024 | 2 |
| 202 | 25796 | 3/19/2024 | 1 |
[on_hand]
| org | code | on_hand |
| 202 | 50662 | 528 |
| 202 | 29314 | 2142 |
| 202 | 74021 | 11770 |
| 202 | 11293 | 3336 |
| 202 | 25796 | 8 |