cancel
Showing results for
Search instead for
Did you mean:
bgolfb 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)

 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:

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

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

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

## 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

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

Daily data points are preferred.

natelpeterson Senior Member

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

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

## 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