problem while multiplying column by measure (doesn't work)

[fsfs]

Hello champs!

To give you initial context:

- I'm working on stock exchange data

- the dim table contains purchases (ticker, amount, price, date, currency, etc)

- the fact table contains all prices of all tickers from dim table from the moment they were bought, plus dividends and splits

- dividends and split data are not continous (these have just 0, unless split/dividend happens)

- split works as follows - if one had 40 stocks and the split was 0.25, the amount of stocks is multiplied by 0.25 and the price is divided by 0.25, its a EV=0 situation, in practice - if the split is tomorrow:

I have 40 stocks today, tomorrow 10 but they are worth the same in total (the price rises automatically x4)

the problem:

- for the sake of calculating the dividends (its paid per stock) I need to know how much of stocks the investor held at a given moment in time, so I need a split-adjusted state of portfolio for any given day

solution I tried:

- I've created a set of measures to have a RUNNING CURRENT AMOUNT with the codes:

Current Split Multiplier =

CALCULATE(

CALCULATE( SUMX(

'stocks-history'

, 'stocks-history'[Stock Splits]

) )

, FILTER(

ALLSELECTED('stocks-history'[_Date])

, 'stocks-history'[_Date] <= MAX('stocks-history'[_Date])

)

)

Current Amount (for dividends) =

SUMX(

investments

, investments[Purchase Amount] * IF( [Current Split Multiplier] = 0, 1, [Current Split Multiplier] ) )

when I put it on matrix, along with dates it looks all good (split is on 23.06.20):

but when I'm trying to create a measure to calculate dividend value:

internal - Dividends =

SUMX(

'stocks-history'

, [Current Amount (for dividends)] * 'stocks-history'[Dividends] )

it ignores the splits and uses the full investments[Purchase Amount]:

I think it is something fundamental and goes along with additional context, probably coming from the IF statement returning static value?, but as a beginner I couldn't figure it out so far

any ideas 🙂 ?

[fsfs · Accepted solution]

I think I might have figured it out

the problem that I had seemed to be because I was mixing the facts (hardcoded data in columns) with the measures in a one measure, in which case the measure tend to have a single value (like with VAR statement)

I fixed it with turning the facts into measures with
( CALCULATE ( SUMX ( table, column_that_i_needed_elsewhere_as_measure), FILTER( calendar_table, calendar_table[date] = MAX(calendar_table[date]) ) ) )

and it seems to work as intended

[Anonymous]

Let's get the basics out of the way first.

"the dim table contains purchases (ticker, amount, price, date, currency, etc)" 

This table is not a dimension table. It's a fact table as well. Dimensions are attributes of the processes you capture. Here you capture Purchases (clearly a process). A dimesion would be: Ticker, Currency, Date... These are dimensions. Fact tables must only store keys to dimensions and figures (statistics) about the process.

As for the calculations... We need data (representative of the problem) to work with in text form. Or a link to a file with data. Then we'll most likely be able to help.

Thanks.

[fsfs]

I'm attaching sample data for both FACT tables (thanks for clarifying 🙂 )

https://drive.google.com/file/d/15QvrFRh3c4T5ANTIQIksHxYJkTRIpcE2/view?usp=sharing

https://drive.google.com/file/d/1rp_Q37ay5asEg8xinvqF9sJSc58FwCNN/view?usp=sharing

just additional context:

- in the attached data the investor hold 10000 shares of 0017.hk until 22.06.2020 (id. 474)

- until then, he is given 10000 x dividend (id. 12 ,id. 260, id. 378)

- from 23.06.2020 (id. 475) the investor hold 2500 shares of 0017.hk and from that moment on this is the base for the dividend amount

- in the whole dataset the prices are adjusted to the splits and dividends backwards (which means that in the reality, the stock price on 22.06.2020 was around 9 HKD, but the data is altered by the data vendor to resemble continuity) - if there was another split tomorrow, lets say 5:1, if I connect to the server tomorrow, all the prices will be divided by 5

now Im thinking about having another fact table with the current state of portfolio, but that seems to lack elegancy 

[daxer-almighty]

@fsfs 

Why does the investments.csv file have just one row? I'd like to see some more... a (small) portfolio of stocks. It would also be useful if you could make some manual calculation(s) for some selection of parameters to exactly show what you're after. I'll try to figure out from your descriptions but I'd like to see some good example.

Another problem is that the Stock Splits column has only 0's in it. I need data that's representative of the problem. This means I have to have actual splits in there.

Thanks.

[fsfs]

it does have a split in it (row 474) and the single stock is representative enough

when it comes to the project - I scrambled it and started from scratch, decided to move on with the continous data for the portfolio (so I created a detailed data of how much the investor had at any given day from the day of purchase), due to the conflicting nature of splits and dividends I believe it cannot be done and unified with the way I described in the OP (it would be possible if there were no splits ever)

still the question remains

how do you access the value of the measure in another measure in a given (changing context)?

the data and measures I've included are the same I've used in the first post and allows to recreate the problem exactly

[Anonymous]

Thanks. I'm going to look at this soon. But I suspect there'll be some back-and-forth since I'll have to acquire some good understanding of the nature of the data. I have a background in financial engineering (which certainly helps) but have not worked in the field for a while.