topic Re: Continuous forecast calculation in DAX Commands and Tips

Continuous forecast calculation

vandavarosi — Tue, 17 Jan 2023 13:14:29 GMT

Hi,

I would like to calculate the next year forecast in my power BI model based on the currectly available data.

Calculation method should be: 3MO AVERAGE forecast * growth date; however, the problem is that the average forecast should be always the last three month before the calculated month.

For example:

I have data for April - May - June based on that I am able to calculate the 3MO average and multiply with the the growth date which is fix.

With the calculation above I will receive the forecasted value for July; however for August I have to calculate the 3MO average of May- June and July and for Sept I have to calculate the 3MO average of June - July and August.

Can you please help?

Re: Continuous forecast calculation

amitchandak — Tue, 17 Jan 2023 13:44:41 GMT

vandavarosi , In power bi recursive calculations like this, are very limited

refer:

https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query/

https://community.powerbi.com/t5/Power-Query/Simple-recursive-calculation-Power-Query/td-p/1093141

https://community.powerbi.com/t5/Desktop/Recursive-Measure-Calculation-Problem-in-Power-BI/m-p/458562

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=8a21f79b-f0b6-42e7-8e97-6b5bbc28d08c&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

Re: Continuous forecast calculation

Anonymous — Wed, 18 Jan 2023 02:06:29 GMT

Hi vandavarosi ,

Please have a try.

Create measures.

Measure = VAR _1 = MAXX ( ALL ( 'Table' ), 'Table'[Date] ) VAR _2 = EDATE ( _1, -3 ) RETURN AVERAGEX ( FILTER ( ALL ( table ), table[date] <= _1 && table[date] >= _2 ), table[3MO] )

How to Get Your Question Answered Quickly

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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