Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Monthly Returns

Hello,

 

I'm new to PowerBI and attempting to calculate cumulative monthly returns based on a net return measure. The below Cumulative Net Return % calculation is calculating an accurate cumulative return across all of the dates, but I need it to display each month on a cumulative basis. In this example, I would expect the Cumulative Net Return % to display -17.64% for 1/31/22 instead of -19.96% for both months.

 

Here is the matrix that PowerBi is returning:

 

And the current cumulative net return % calculation:

 

How can I make the calculation running instead of the same for each row? I also need the formula to exclude any months that are not included - it's currently working in that way. The issue now is making it calculate by row.

  • Anonymous's avatar
    Anonymous
    4 years ago

    I was actually able to use this formula succesfully, my date filters were referencing the wrong table. They say offshore but should say onshore.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi , Anonymous 

    How did you get value of these three columns  ? Are they regular columns ? What is the calculation logic for that column you need ?Can you provide a detailed data model for our reference ?

    Best Regards,
    Community Support Team _ Ailsa Tao
    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
      4 years ago

      I was actually able to use this formula succesfully, my date filters were referencing the wrong table. They say offshore but should say onshore.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        The dax measure that worked:

         

        Cumulative Net Return % Onshore =
        CALCULATE(
        PRODUCTX(ALLSELECTED('COMBINED ONSHORE'[End Date]), (1+'DAX MEASURES'[Net Return % Onshore]))-1,
        FILTER(
        ALLSELECTED('COMBINED onsHORE'[End Date]), 'COMBINED OnSHORE'[End Date]<=MAX('COMBINED ONSHORE'[End Date])
        )
        )