Forecast based on variables and switching between forecast and actual revenue

[TK12345]

I am making a forcast for the upcomming months, but i can't really get it in Power BI.
I am trying it with some variables but it does not work yet. I would like to make the measure for the Revenu.
But the dificult part is that today we are in month 11, so we need to have our Revenu forcast based on the ACTUAL Revenu of Month 10 (oktober)
So when we are still in month 11, the revenu forcast for februari is still based on Januari that is based on forecast december and that one is based on
forecast november and that one is based on ACTUAL Oktober. But when we are in December next month we do have the actuals for November and then
all the forecasts needs to look at the actuals of november. So the november will be orange as well.


So at the start of the month all the forecasts needs to change because we need to have our forecast look at our last whole month revenue. Please see atached. https://1drv.ms/x/s!ArweH82sfYtEnFDgk9wCrDhzVTST?e=8caRnq 

Please help me out. 
So the forecast of the first upcoming month is based on the ACTUAL REVENUE last month. And the forecasts months after the first one are based on the forecast of the previous month. 

[v-yangliu-msft]

Hi  @TK12345 ,

I created some data:

Here are the steps you can follow：

1. Create a calculated column in Table.

Table_column =
var _loss=CALCULATE(SUM('Table'[Loss]),FILTER(ALL('Table'),[date]<=EARLIER('Table'[date])))
var _rev=CALCULATE(SUM('Table'[Pot.Rev]),FILTER(ALL('Table'),[date]<=EARLIER('Table'[date])))
return
IF([Revenu Forecast]=BLANK(),600000+_rev-_loss,[Revenu Forecast])

2. Enter the power query, copy the Table to form a new table Unpivot Table

3. Select [Revenu Forecast],[Loss],[Pot.Rev],[Marge IN], [Marge IN.1] in the Unpivot Table in the New table, and click Transform – Unpivot Columns.

Result:

4. Create a calculated column in the Unpivot Table.

Expected =
IF([Attribute]="Revenu Forecast",CALCULATE(SUM('Table'[Table_column]),FILTER(ALL('Table'),[Attribute]="Revenu Forecast"&&[date]=EARLIER('Unpivot Table'[date]))),[Value])

5. Result:

Best Regards,

Liu Yang

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

[TK12345]

Hi there, thanks for your reply!
It almost helps! But important to know is that I don't have the measure [Revenue Forecast] or column. Those are based on my last whole month revenue. 

So I have a measure for the Potentional loss per month, a measure for the potentional Revenue per month, and the last known whole Revenue. So my forecast is not empty for other months, it just has to be made and based on my last known revenue.