Multiply previous month by a compounded return measure

[Anonymous]

Hello community,

Hope you are well.

I am hoping someone can help me - I've scoured the internet to try and find a solution on this.

I am trying to achieve the following in Power BI using DAX (if it is even possible)

I have data up till a certain month of the year (Return %). I then have a measure called Compounded Return built off parameters where the values can vary based on the user selecting whatever % they want. I am trying to achieve column Forecasted Return in Power BI but keep struggling using that formula:

Cell E8 = (1+C9)*(1+D9)-1
Cell E7 = (1+E8)*(1+D8)-1 (This is then dragged up till the end of the year using E8 as the next reference)

I've tried numerous PRODUCT and CALCULATE measures but don't come anywhere close to the result. I do have a Date Dimension table in the model. Please help if possible.

Thank you.

[amitchandak]

@Anonymous , with date table, assuming return and compounded return are measures. else use aggregation on them

new Meaure =

(1+ CALCULATE([Return],DATESMTD(dateadd('Date'[Date],-1,MONTH)))) *( 1+ CALCULATE([compounted Return],DATESMTD(dateadd('Date'[Date],-1,MONTH)))) +1

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hello @amitchandak  - thank you for the help - appreciate it.

The measure you provided does it for the next month only from the previous month in the Return column - the rest of the year equals the compounded return only - see below

[amitchandak]

@Anonymous .Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak thank you, i've pasted a link below:

https://drive.google.com/drive/folders/1T5EN-Vbmq5oJgGNdU9DBpbmjwDQQrRh3?usp=sharing

The first sheet in the Excel is the data imported to Power BI. The second sheet is what I am trying to achieve in Power BI.

Let me know if you have issues accessing the files.