Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.  Portfolio.PNG

 

Alfa.PNG

 

Thank you very much!

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

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]

8.png

 

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
Not applicable

Hi @v-juanli-msft,

 

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

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
Not applicable

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

 

Captura.PNG

v-juanli-msft
Community Support
Community 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.

1.png

But it is static finally.

 

Best Regards

Maggie

 

 

Anonymous
Not applicable

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.

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.