Calculate returns with monthly data points using daily data points

[Anonymous]

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?

[Anonymous]

@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

[Anonymous]

Daily data points are preferred. 

[Anonymous · Accepted 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]

@Anonymous

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