Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 thre...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Hi v-juanli-msft,

 

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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