Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to calculate a return of a portfolio of 5 securites (Table: myPtf) and compare it with the return of an index (Table: Index).
Securities have monthly data points, whereas the index has daily data points.
A merged table with index and myPtf would look like this: (note that myPtf would be a sum of 5 different securities)
DateIndex | Index | DatePtf | myPtf | m_myPtf | m_StartDate | m_IndexReturn | m_PtfReturn |
01.01.2018 | 100 | 01.01.2018 | 200 | 200 | 200 | 0.00 | 0 |
02.01.2018 | 102 | 200 | 200 | 0.02 | 0 | ||
…. | … | 200 | 200 | … | 0 | ||
31.01.2018 | 110 | 200 | 200 | 0.10 | 0 | ||
01.02.2018 | 108 | 01.02.2018 | 195 | 195 | 200 | 0.08 | -0.03 |
02.02.2018 | 107 | 195 | 200 | 0.07 | -0.03 | ||
… | …. | 195 | 200 | … | -0.03 | ||
01.03.2018 | 115 | 01.03.2018 | 180 | 180 | 200 | 0.15 | -0.10 |
02.03.2018 | 114 | 180 | 200 | 0.14 | -0.10 |
The return measures would then be used in a line chart with DateIndex as x-axis with DateIndex also as Between Slicer.
Since dax is still a bit of black box for me, Im having difficulties creating the following 2 measures (not calculated columns):
- m_myPtf that keeps the value from the last non blank data point
- m_StartDate that keeps the myPtf value from the start date
I have managed to create m_myPtf, but it only works for a single security and wont work if I want to see the performance of myPortfolio:
I have more problems with the m_StartDate:
Any guidelines on what im doing wrong?
Solved! Go to Solution.
@Anonymous -
You could use Fill Down in Power Query to populate a daily value for your index based on your last non-blank value. That way, you don't really need your first measure.
Then, modify your second measure, slightly:
m_StartDate = VAR minDate = MIN(myPtfs[DatePtf]) RETURN CALCULATE( SUM([myPtf]), Index[DateIndex]=minDate )
Hope this Helps!
Nathan
@Anonymous - Before getting into the DAX, let's talk about the model: Do you care about the daily index, or could it be summarized to a monthly value (first date value, last date value, median value, mean value)?
If you store a single index value per month, it would make later tasks much more straightforward.
Cheers!
Nathan
Daily data points are preferred.
@Anonymous -
You could use Fill Down in Power Query to populate a daily value for your index based on your last non-blank value. That way, you don't really need your first measure.
Then, modify your second measure, slightly:
m_StartDate = VAR minDate = MIN(myPtfs[DatePtf]) RETURN CALCULATE( SUM([myPtf]), Index[DateIndex]=minDate )
Hope this Helps!
Nathan
@Anonymous
Merging the tables in power query, pivoting, then unpivoting and lastly filling down did the trick to avoid using both measures. Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |