Forum Discussion
Cumulative product - Index base 100 dynamic base
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-msft7 years ago
Community Support
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
- Anshenterprices4 years ago
Helper IV
amitchandak I am having same above requirement, can you please help me on the same.