Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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
….…  200200…0
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?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Daily data points are preferred. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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