calculate the difference between the stocks buy-price and the sale-price with DAX

[bk11]

Hi all,

I have a pretty complicated DAX question. It's possible that it's too much to ask for when it comes to DAX, so I'll accept this kind of answer as well.

I have a table with all of the transactions in my investment portfolio.

I am trying to calculate the difference between the stocks buy-price and the sale-price.

In order to do so, I'll have to check (for each symbol), how many stocks and in how many times I bought before the first sale.

Here is how I would do it:

Table:

Symbol	type	Units	date	value per unit
wix	buy	5	01/01/2014	100
wix	buy	10	01/05/2016	200
AMD	buy	55	01/07/2018	100
AAPL	buy	13	05/06/2016	300
wix	sell	6	01/08/2016	500
AAPL	sell	13	01/07/2016	120
wix	buy	20	01/01/2017	800
wix	sell	22	01/05/2017	700

For each symbol, I would sum the stocks I got before the first sale. Let's take wix for example.

I check the dates for rows of wix + buy:

wix + buy:		wix+sell:
01/01/2014	<	01/08/2016

yes -->

x+=units*value per unit

01/05/2016

<

01/08/2016

yes -->

x+=units*value per unit

01/05/2017

<

01/08/2016

no, return the sell units * value per unit from 01/08/2016 minus x (which provides you the buy vs. sell difference).

Then I'll have to keep checking against the next sale date:

01/01/2017

<

01/05/2017

yes --> x+=value

and so on...

That's basically what I'm looking for:

(just to explain better the solution I'm looking for-

the value of the stock over time:

	symbol	new units	new value per unit
1	wix	5	100
2	wix	15	166.6666667
3	wix	9	166.6666667
4	wix	29	603.4482759

1.
sell	6*500 =	3000
own	6*(2500/15)=	-1000
Margin		2000

2.
sell	22*700 =	15400
own	22*(new value #4)=	-13275.86207
Margin		2124.137931

That's the value I'd like to return: 2124.137931

What do you guys think? too complicated?

Thanks!

[AZFinserv]

Need help on an additional scenario

1) Multiple 'buys' or 'sells' on the same day

2) 'Buy' and 'Sell' on the day - intraday transaction

Please suggest how would I do that?

Thanks in advance

@Zubair_Muhammad 

@LivioLanzo 

@bk11 

[LivioLanzo]

@bk11

Could you tell me how are you calculating the avg price of 603.4482759 in #4

On my side I have:

534.4828

[bk11]

Hi, thank you for your response!
After the first sale I was left with 9 until, each worth 166.666667.
Afterwards, I bought another 20 for 800 each.
Therefore, the new balance and average price is:
(9*166.666667+20*800)/29= 603.44

Hope that makes sense.

[Anonymous]

---

@bk11 Have you tried anything so far?
When do you want to calculate this? On data load?
If it is on data load, I think it can be done with M. Is this an option?

---

[bk11]

Hi @Anonymous and @LivioLanzo, thank you for your help!

I am loading this data from excel. I can calculate it during load time or afterwards, it doesn't really matter.

I am also open to see new ways to calculate the average price. In fact, if I could find a way to return the sum of stocks I bought before the sale date it would be enough because then I could do some independent calculations using measures to find the average. 

So just a way to return for wix for example:

buy: (5*100 + 10*200) = 2500 (before the first sale) so I can afterwards put it in a matrix next to the sale and then calculate the difference.

Thank you!

[LivioLanzo]

Hi  @bk11

I am assuming you want to calculate the realized gain from a sale base on the avg pride of the security ? 

This could be done either in a calculated column but also dynamically in a measure. 

[bk11]

Hi @LivioLanzo,

Yes, that's what I'm trying to do based on the table I included in my original question.

How would you do it with a measure/ calculated table so it will dynamically reflect the profit in each sale?

[LivioLanzo]

You can do it like this in a calculated column:  @bk11

assuming no short positions

Realized =
IF (
    Trades[type] = "SELL",
    VAR avgPrice =
        CALCULATE (
            DIVIDE (
                SUMX (
                    Trades,
                    Trades[value per unit] * Trades[Units]
                        * IF ( Trades[type] = "SELL", -1, 1 )
                ),
                SUMX ( Trades, Trades[Units] * IF ( Trades[type] = "SELL", -1, 1 ) )
            ),
            ALLEXCEPT ( Trades, Trades[Symbol] ),
            Trades[date] < EARLIER ( Trades[date] )
        )
    RETURN
        ( Trades[value per unit] - avgPrice )
            * [Units],
    BLANK ()
)

[YShikita]

Hi @LivioLanzo ,

I am using your formula as a base, and trying to add a filter to calculate separately for each client. (As your formula is for individual use)

My transaction data includes all clients' daily transactions, so I would have to filter and calculate by each client.

The error says "The syntax for ')' is incorrect".

Could you please point out the errors from the formula below, and tell me how to correct? Thank you in advance.

Realized =

IF (

VAR myClient = 'ORDER_HISTORY'[A/C Number]

VAR mySymbol = 'ORDER_HISTORY'[Stock Code]

VAR myUniqueOrder = 'ORDER_HISTORY'[ContractNo]

VAR myOrderType = ORDER_HISTORY[Transaction Type]

VAR myFilter = FILTER(

'ORDER_HISTORY',

'ORDER_HISTORY'[A/C Number] = myClient &&

'ORDER_HISTORY'[Stock Code] = mySymbol &&

'ORDER_HISTORY'[ContractNo] <= myUniqueOrder &&

'ORDER_HISTORY'[Transaction Type] = "SELL"

)

* IF (

'ORDER_HISTORY'[Transaction Type] = "SELL",

VAR avgPrice =

CALCULATE (

DIVIDE (

SUMX (

ORDER_HISTORY,

ORDER_HISTORY[Unit Price (incl. Costs)] * ORDER_HISTORY[Qty]

* IF ( ORDER_HISTORY[Transaction Type] = "SELL", -1, 1 )

),

SUMX ( ORDER_HISTORY, ORDER_HISTORY[Qty] * IF ( ORDER_HISTORY[Transaction Type] = "SELL", -1, 1 ) )

),

ALLEXCEPT ( ORDER_HISTORY, ORDER_HISTORY[Stock Code] ),

ORDER_HISTORY[Value Date] < EARLIER ( ORDER_HISTORY[Value Date] )

)

RETURN

( ORDER_HISTORY[Unit Price (incl. Costs)] - avgPrice )

* ORDER_HISTORY[Qty],

BLANK ()

)

 

[bk11]

Hi @LivioLanzo,

Thank you for your response.

I think that I haven't described my request in the right way. I did some more research and understood exactly how I should calculate my stock profit using the Average Cost Basis Method or FIFO Cost Basis Method.

I looked everywhere online and couldn't find a dynamic way to solve this issue:

So for the first table, I described in the first post:

Symbol	type	Units	date	value per unit
wix	buy	5	01/01/2014	100
wix	buy	10	01/05/2016	200
AMD	buy	55	01/07/2018	100
AAPL	buy	13	05/06/2016	300
wix	sale	6	01/08/2016	500
AAPL	sale	13	01/07/2016	120
wix	buy	20	01/01/2017	800
wix	sale	22	01/05/2017	700

I want to calculate the profit made during each sale, using the FIFO method like that:

Symbol	type	Units	date	value per unit	Total value:	Cost Basis with FIFO:
wix	buy	5	01/01/2014	100	500
wix	buy	10	01/05/2016	200	2000
wix	sale	6	01/08/2016	500	3000	2300
wix	buy	20	01/01/2017	800	16000
wix	sale	22	01/05/2017	700	15400	3200
AAPL	buy	13	05/06/2016	300	3900
AAPL	sale	13	01/07/2016	120	1560	-2340

I calculated the Cost Basis with FIFO like this:

2300=(6*500)-(5*100+1*200)

3200=15400-(9*200+13*800)

-2340=1560-3900

The real challenge would be taking what's left from each group of shares which were bought on the same date and subtract it from couple (or more) sales respectively.

Thanks for the help again!

Ben

[LivioLanzo]

@bk11

The solution i provided returns the realized gain & loss based on the AVG price. Is it not what you were after?

[bk11]

Hi  @LivioLanzo,

Thank you for your help! I researched a bit more what is the most common way to calculate the profit and mainly for tax reasons I'll have to use fifo and not average... 

[Ashish_Mathur]

Hi,

This seems like a tough one to oslve with DAX.  I have a solution using regular Excel formulas though.  You may refer to my solution - Valuing Closing Stock using FIFO method of Accounting.

Hope this helps.

[Zubair_Muhammad · Accepted solution]

@bk11

I think this calculated column will be close

It works with sample data

Cost Basis with FIFO =
VAR myunits = [Units]
VAR Previous_buys =
    FILTER (
        Table1,
        [Symbol] = EARLIER ( [Symbol] )
            && [date] < EARLIER ( [date] )
            && [type] = "buy"
    )
VAR Previous_sales =
    SUMX (
        FILTER (
            Table1,
            [Symbol] = EARLIER ( [Symbol] )
                && [date] < EARLIER ( [date] )
                && [type] = "sale"
        ),
        [Units]
    )
VAR Previous_buys_balance =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Previous_buys,
            "Cumulative", SUMX ( FILTER ( Previous_buys, [date] <= EARLIER ( [date] ) ), [Units] )
        ),
        "Balance Left", [Units]
            - IF (
                [Cumulative] < Previous_sales,
                [Units],
                VAR previouscumulative = [Cumulative] - [Units]
                RETURN
                    IF ( Previous_sales > previouscumulative, Previous_sales - previouscumulative )
            )
    )
VAR CostUsed =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Previous_buys_Balance,
            "MyCumulatives", SUMX (
                FILTER ( Previous_buys_balance, [date] <= EARLIER ( [date] ) ),
                [Balance Left]
            )
        ),
        "Balance Used", IF (
            [MyCumulatives] < myunits,
            [MyCumulatives],
            VAR previouscumulatives = [MyCumulatives] - [Balance Left]
            RETURN
                IF ( myunits > previouscumulatives, myunits - previouscumulatives )
        )
    )
RETURN
    IF (
        [type] = "sale",
        [Units] * [value per unit]
            - SUMX ( CostUsed, [Balance Used] * [value per unit] )
    )

[ziomzbronxu]

Hi,

@bk11 @Zubair_Muhammad 

There is small error in calculation of Balance Used (it counted cumulative). 

Also to correctly process multiple trades in a day use timestamp instead of transaction date, or if absent, sort them using {date, type} during loading - buys before sells and add artificial index column, then use this instead of date.

The corrected version:

Cost Basis with FIFO = 
VAR myunits=[Units]
VAR Previous_buys = 
	FILTER( 
		Table1,	
		[Symbol] = EARLIER([Symbol]) 
			&& [date] < EARLIER([date]) 
			&& [type] = "buy"
	)
	
VAR Previous_sales =
	SUMX(
		FILTER(
			Table1,
			[Symbol] = EARLIER([Symbol])
				&& [date] < EARLIER([date])
				&&[type] = "sale"
			),
		[Units]
	)
	
VAR Previous_buys_balance = 
	ADDCOLUMNS(
		ADDCOLUMNS(
			Previous_buys,
			"Cumulative",
			SUMX(
				FILTER(
					Previous_buys,
					[date] <= EARLIER([date])
				),
				[Units]
			)
		),
		"Balance Left",
		[Units] - IF(
			[Cumulative] < Previous_sales,
			[Units],
			VAR previouscumulative = [Cumulative]-[Units] 
			RETURN IF (Previous_sales > previouscumulative, Previous_sales - previouscumulative)
		)
	)
	
VAR CostUsed = 
	ADDCOLUMNS(
		ADDCOLUMNS(
			Previous_buys_Balance,
			"MyCumulatives",
			SUMX(
				FILTER(
					Previous_buys_balance,
					[date] < EARLIER([date])
				),
				[Balance Left]
			)
		),
		"Balance Used",
		IF( 
			[Balance Left] <= myunits - [MyCumulatives], 
			[Balance Left],
			IF ( myunits > [MyCumulatives], myunits -[MyCumulatives])
		)
	)

RETURN
IF ( [type] = "sale", 
	[Units] * [value per unit] - SUMX( CostUsed, [Balance Used] * [value per unit])) 

Results:

And the pbix test:

ExamplePbi.pbix 

For some weird reason this doesn't work correctly in PowerPivot for Excel...  Compiles, just calculates wrong....

[LivioLanzo]

@bk11

I see what you mean now, but I am not sure that is the correct way to get the average price.   Unless you are using the FIFO method,? but I guess even then we would not match