cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bgolfb Frequent Visitor
Frequent Visitor

Calculate returns with monthly data points using daily data points

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)

DateIndexIndexDatePtfmyPtfm_myPtfm_StartDatem_IndexReturnm_PtfReturn
01.01.201810001.01.20182002002000.000
02.01.2018102  2002000.020
….  2002000
31.01.2018110  2002000.100
01.02.201810801.02.20181951952000.08-0.03
02.02.2018107  1952000.07-0.03
….  195200-0.03
01.03.201811501.03.20181801802000.15-0.10
02.03.2018114  1802000.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:

m_myPtf =
VAR curDate = MAX(Index[DateIndex])
RETURN
    CALCULATE(
// SUM cumulates the values (i.e. would return 395 in February)
        MAX(myPtfs[myPtf]),
        FILTER(ALLSELECTED(Index),Index[DateIndex]<=curDate)
    )


I have more problems with the m_StartDate:

m_StartDate =
VAR minDate =
    MIN(myPtfs[DatePtf])
RETURN
    CALCULATE(
        [m_myPtfs],
        Index[DateIndex]=minDate
)


Any guidelines on what im doing wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
natelpeterson Senior Member
Senior Member

Re: Calculate returns with monthly data points using daily data points

@bgolfb  -

 

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

4 REPLIES 4
natelpeterson Senior Member
Senior Member

Re: Calculate returns with monthly data points using daily data points

@bgolfb - 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

bgolfb Frequent Visitor
Frequent Visitor

Re: Calculate returns with monthly data points using daily data points

Daily data points are preferred. 

natelpeterson Senior Member
Senior Member

Re: Calculate returns with monthly data points using daily data points

@bgolfb  -

 

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

bgolfb Frequent Visitor
Frequent Visitor

Re: Calculate returns with monthly data points using daily data points

@natelpeterson

Merging the tables in power query, pivoting, then unpivoting and lastly filling down did the trick to avoid using both measures. Thanks