Cumulative product - Index base 100 dynamic base

[Anonymous]

Hello everybody,

I kindly ask for someone's help to solve the following:

I have a table with four columns: IDAnalyst, Date, Portfolio (return) and Benchmark (return). I have to calculate three additional measures. An index base 100 for each portfolio and benchmark columns with a dynamic base and with the possibility to be filtered by analyst with a slicer (as well as by initial date and end date). Additionally, I have to calculate the cumulative alpha (difference between portfolio and benchmark returns: Portfolio - Benchmark). There is no date for every calendar year's dates in the Dates column. Please find attached two screenshots with an example of how I am doing it in excel.  

Thank you very much!

[v-juanli-msft]

Hi @Anonymous 

1.Create a calendar date table, create correct relationship(one to many, both direction) between two tables, add date from this table in the slicer.

calendar = CALENDARAUTO()

2. open edit queries, keep the order as your screenshot, then add an index column,

close&&apply

3.create measures in your main table

selected date = MAX(Query1[Date])

p clc = CALCULATE(SUM(Query1[Portfolio]),FILTER(ALLSELECTED(Query1),Query1[Index]<=MAX(Query1[Index])))

p index = IF([selected date]=BLANK(),BLANK(),100*(1+[p clc]))

b clc = CALCULATE(SUM(Query1[Benchmark]),FILTER(ALLSELECTED(Query1),Query1[Index]<=MAX(Query1[Index])))

b index = IF([selected date]=BLANK(),BLANK(),100*(1+[b clc]))

percentage = [p clc]/[id measure]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft,

To which measure are you referring to with [id measure] in the percentage measure?

[v-juanli-msft]

Hi @Anonymous 

I really apologize for missing your email before.

When you open my file, you will see 

id measure = SUM(Query1[ID Analyst])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft,

Thank you for answering. I am still having trouble with the results. As you can see, I did the same exercise in Excel with the same numbers as you did in the attached file for analyst 7 and the results are not the same. This may seem like a small difference, but when you compound the results it amounts for a lot. Please find attached an image with the example.

Best regards,

Juan

[v-juanli-msft]

Hi @Anonymous 

"each portfolio and benchmark columns with a dynamic base and with the possibility to be filtered by analyst with a slicer (as well as by initial date and end date)"

Could you show an example how the portfolio index and benchmark index should change with the date slicer?

I can go to Edit queries to add custom columns and do some transformation to get the table as yours.

But it is static finally.

Best Regards

Maggie

[Anonymous]

Hello @v-juanli-msft ,

Thank you for answering. Let's say for example I have data ranging from 01/01/2017 to today. Initially, if I make the calculation from 01/01/2017, I would start with a base of 100, and it would change sequentially with each new portfolio/benchmark return. But, I would like to be able to use a slicer to make the calculation, say, for example, since 01/01/2018. Then, the base of 100 would start on that date and change sequentially. As I have returns for as many number of analysts I have in my database, and each analyst's portfolio and benchmark is independent from each other, I would like to be able to filter by analyst as well. So one analyst may exist in the database since 01/01/2017, and another since 01/06/2018. Then, if I filter by analyst, the benchmark and portfolio indexes, as well as the alpha will be calculated specifically for that analyst in the range of dates filtered as well, starting with base 100 in the initial date.

These two filters, analyst and date, I would like to apply in the calculation of the benchmark and portfolio indexes and subsequently the alpha, which is simply the difference between both.

What I can see from your example though, is that the index is not being calculated in a cumulative way. 

Best regards.

[Anshenterprices]

@amitchandak  I am having same above requirement, can you please help me on the same.

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie