Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate return with base value/index

Hi,   For a performance dashboard I m trying to create a measure that calculates the return based on a selected period. If I want to calculate the return from 31-01-2021 till 30-04-201 then we hav...
Need Help
  • tex628's avatar
    tex628
    5 years ago

    Try something along the lines of this:

     

    Measure =
VAR minDate = MIN(Calendar[Date])
VAR maxDate = MAX(Calendar[Date])
PRODUCTX(
SUMMARIZE( 
FILTER( ALLSELECTED(Table) , [Date] >= minDate && [Date] <= maxDate ) ,
[Date] , 
"Value" ,
IF( [Date] = minDate , 1 , 1 + [Monthly Return] )
) , 
[Value]
)

     

    Br,
    J

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

The basic calculation you want to do is:

 

Investments_RETURN = 
PRODUCTX ( 
    Fact_Return,
    1 + Fact_Return[Monthly_Return]
) - 1

 

I have ignored the issue of excluding the first date in the selected range - but you could hand this by filtering Date appropriately. Would it be acceptable to just filter on just the months whose return you want included? If so, you could leave the above formula unchanged.

 

There might be some other tweaks to produce the exact result you want, but hopefully that basic formula structure helps.

 

Regards,

Owen

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi OwenAuger,

     

    Thanks for your reply and help. The formule gives me a good starting point to create the final measure.

    The most difficult part for me is how to ,disable, the first month and replace it by 1 (100%)

     

    PRODUCT(FACT_RETURN[RETURN]) 

     

    A little bit more in detail, assume that I have a dataset where I have the return on daily basis, but I only want the values for the last day of the month (I think EOMONTH will fit here). Is a virtual table a possibe solution to make a selection?

     

    The concept of the final measure would be  Return=100% + (Period). So the period is confusing me 🙂 

     

    Kind regards

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      5 years ago

      Hi again Anonymous 

      First off, your data model should include a Date table related to your 'return' table, to facilitate any date-related filtering or calculations.

       

      To help answer your question, assuming you have daily returns, could you show how you would expect a typical report to look in table form, and how you want the end user to apply filters?

       

      It sounds like you want to see monthly returns, and would you want users filtering by month as well?

       

      Regards,

      Owen

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi OwenAuger 

         

        I really appriciate your reply and offer to help! Thanks

        In our case the startdate (which can be  selected with a filter in the report) will always be 100%

        There is a date table in the datamodel for filtering.

        Lets assume that we want to see the results of the return for a period of six month. The endate would be 31-3-2021, which can also be selected in the filter.  

        I hope the table below gives a better understanding.

         

         

        Thanks again for your help!