Continuous forecast calculation

[vandavarosi]

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? 

[Anonymous]

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.

[amitchandak]

@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/45856...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=8a21f79b-...