Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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] ) )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |