Forum Discussion
problem while multiplying column by measure (doesn't work)
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
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
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.