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 have to multiply 100% * 3%*-1%*1.8%*0.4%

When we start on 28-2-2021 I expact that the measure would multiply 100%*-1%*1.8%*0.4%. 

So the MIN date value of the selected period always has to be 100% (or 1). Furthermore it could also be that we want to know 

the return between feb and april. In that case the calculation has to be 100%*-1%*1.8%.

 

For calculate the return Im using 

Investments_RETURN = PRODUCT(Fact_Return[Monthly_Return])
 
Could someone help, really stuck here :S 
 
Thanks in advance
 
Kind regards!

 

DateProductIdMonthly  Return
31-01-202112.5%
28-02-202113%
31-03-20211-1%
30-04-202111.8%
31-5-202110.4%

 

tex628 Greg_Deckler Zubair_Muhammad 

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

5 Replies

  • 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