Calculate return with base value/index

[Anonymous]

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!

Date	ProductId	Monthly  Return
31-01-2021	1	2.5%
28-02-2021	1	3%
31-03-2021	1	-1%
30-04-2021	1	1.8%
31-5-2021	1	0.4%

@tex628 @Greg_Deckler @Zubair_Muhammad 

[OwenAuger]

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]

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]

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]

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!

[tex628 · Accepted solution]

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