Consolidated running balance from two fact tables

leolapa_br — Tue, 22 Aug 2023 16:11:57 GMT

I have this simple data model w/ two fact tables that pertain to purchases and sales of shares of stock:

I need to calculate a running balance by taking a running sum of shares purchased by ticker and SUBTRACT by the running sum of shares sold by ticker.

I tried this:

Shares balance 1 = VAR Date_Ref = MAX( dDates[Date] ) VAR Tick_Ref = VALUES( dAssets[Ticker] ) VAR Cumm_Purch = CALCULATE( SUMX( fPurch, fPurch[Shares] ), fPurch[Ticker] = Tick_Ref, fPurch[Date] <= Date_Ref ) VAR Cumm_Sold = CALCULATE( SUMX( fSales, fSales[Shares] ), fSales[Ticker] = Tick_Ref, fSales[Date] <= Date_Ref ) VAR Result = Cumm_Purch - Cumm_Sold RETURN Result

And I also tried this:

Shares balance 2 = VAR Date_Ref_Purch = MAX( fPurch[Date] ) VAR Tick_Ref_Purch = VALUES( fPurch[Ticker] ) VAR Cumm_Purch = CALCULATE( SUMX( fPurch, fPurch[Shares] ), fPurch[Ticker] = Tick_Ref_Purch, fPurch[Date] <= Date_Ref_Purch ) VAR Date_Ref_Sold = MAX( fSales[Date] ) VAR Tick_Ref_Sold = VALUES( fSales[Ticker] ) VAR Cumm_Sold = CALCULATE( SUMX( fSales, fSales[Shares] ), fSales[Ticker] = Tick_Ref_Sold, fSales[Date] <= Date_Ref_Sold ) VAR Result = Cumm_Purch - Cumm_Sold RETURN Result

And both produce the same WRONG results, as shown on the sample table that follows...

For this visual the 'Ticker' column comes from the dimension table dAssets and the 'Date' column comes from the date table.

And the red column I manually added to show the numbers I was supposed to compute:

Ticker	Date	Shares purchased	Shares sold	Shares balance 1	Shares balance 2	Correct result
ABEV	3/1/2021	77		77	77	77
ABEV	5/4/2021	73		73	73	150
ABEV	6/4/2021		150	-150	-150	0
ABEV	6/28/2021	62		62	62	62
ABEV	10/29/2021		62	-62	-62	0
ALSO3	1/7/2021	39		39	39	39
ALSO3	2/3/2021		39	-39	-39	0
ALSO3	3/1/2021	45		45	45	45
ALSO3	5/11/2021		45	-45	-45	0
ALSO3	6/29/2021	36		36	36	36
ALSO3	1/7/2022	56		56	56	92
ALSO3	3/30/2022		92	-92	-92	0

I suppose something on either code is messing up the results, what could that be?

Re: Consolidated running balance from two fact tables

ERD — Tue, 22 Aug 2023 17:03:59 GMT

Hi leolapa_br ,

You can try this (you were close):

Shares balance = VAR Date_Ref = MAX('Date'[Date]) VAR Tick_Ref = MAX(dAssets[Ticker]) VAR Cumm_Purch = CALCULATE( SUM(fPurch[Sharespurchased]), dAssets[Ticker] = Tick_Ref, 'Date'[Date] <= Date_Ref ) VAR Cumm_Sold = CALCULATE( SUM(fSales[Sharessold]), dAssets[Ticker] = Tick_Ref, 'Date'[Date] <= Date_Ref ) VAR Result = IF([Shares purchased] <> BLANK() || [Shares sold] <> BLANK(), Cumm_Purch - Cumm_Sold) RETURN Result

topic Re: Consolidated running balance from two fact tables in DAX Commands and Tips

Consolidated running balance from two fact tables

Re: Consolidated running balance from two fact tables