Using rolling average to estimate future growth

[Anonymous]

Hi,

I tried and tried, but I simply can't find the answer. I have a dataset which sums the AUM of our clients during the past 12 years. I want to create a prediction of how our AUM may look like one year from today, based on a moving average from the last 12 months.

Notice, my datasett contains the sum of the AUM for all our clients for each day, not individual holdings for each client. Any ideas?

Thanks!

[Icey]

Hi @Anonymous ,

How about using a custom visual - Forecasting with ARIMA or create a R visual?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey @amitchandak 

I can imagine that there is easy way to just calculate the moving average of the data I already have?

As I am a beginner, I simply just don't know the code yet, but it should be straight forward?

Thanks, 

[Anonymous]

Hi @amitchandak,

Thank you for your reply! I tried using your formula, but it actually return the exact same value as I had before.

There is no difference between the graph if I use my old value "MarketValue", or your formula "Rolling 12". I want to use the last 12 month to estimate the next 12. Any suggestions?

Thanks!

[amitchandak]

@Anonymous , not very clear try with a date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

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.