DAX consolidated running balance from two fact tables

Question

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 'Correct result' 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?

score 0 · Answer 1 · answered Aug 22 '23 at 17:54

Answer from an user in another forum...

Shares purchased = 
VAR Tick_Ref = VALUES( fPurch[Ticker] )
VAR Result = 
    CALCULATE( 
        SUMX( 
            fPurch, 
            fPurch[Shares] 
        ), 
        fPurch[Ticker] = Tick_Ref 
    )
RETURN
    Result

Shares sold = 
VAR Tick_Ref = VALUES( fSales[Ticker] )
VAR Result = 
    CALCULATE( 
        SUMX( 
            fSales, 
            fSales[Shares] 
        ), 
        fSales[Ticker] = Tick_Ref 
    )
RETURN
    Result

Shares balance = 
VAR Tick_Ref = VALUES( dAssets[Ticker] )
VAR Date_Ref = MAX( dDates[Date] )
VAR Acumm_Purch = 
    CALCULATE( 
        SUM( fPurch[Shares] ), 
        dAssets[Ticker] = Tick_Ref, 
        dDates[Date] <= Date_Ref 
    )
VAR Acumm_Sold = 
    CALCULATE( 
        SUM( fSales[Shares] ), 
        dAssets[Ticker] = Tick_Ref, 
        dDates[Date] <= Date_Ref 
    )
VAR Result = 
    IF( 
        [Shares purchased] <> BLANK() || 
        [Shares sold] <> BLANK(), 
        Acumm_Purch - Acumm_Sold 
    )
RETURN
    Result

DAX consolidated running balance from two fact tables

1 Answers1