Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bk11
Frequent Visitor

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

 

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:

SymboltypeUnitsdatevalue per unit
wixbuy501/01/2014100
wixbuy1001/05/2016200
AMDbuy5501/07/2018100
AAPLbuy1305/06/2016300
wixsell601/08/2016500
AAPLsell1301/07/2016120
wixbuy2001/01/2017800
wixsell2201/05/2017700

 

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:

 

 symbolnew unitsnew value per unit
1wix5100
2wix15166.6666667
3wix9166.6666667
4wix29603.4482759

 

1.  
sell6*500 =3000
own6*(2500/15)=-1000
Margin 2000

 

2.  
sell22*700 =15400
own22*(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!

1 ACCEPTED SOLUTION

@bk11

 

I think this calculated column will be close

 

It works with sample data Smiley Wink

 

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] )
    )

fifo.png


Regards
Zubair

Please try my custom visuals

View solution in original post

16 REPLIES 16

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.