Forum Discussion
Cumulative product - Index base 100 dynamic base
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!
8 Replies
v-juanli-msftCommunity Support
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
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.
- 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
- 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
MaggieCommunity 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.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
MaggieCommunity 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.