Cumulative Monthly Returns

[Anonymous]

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]

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 · Accepted solution]

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]

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])

)

)