Rolling forecast cutoff date based on slicer

[lekkerbek]

Hi,

Hope someone can help me with this.

I created a P&L and budget in a matrix visual.

I would like to create a P&L rolling forecast in a matrix where the column heads are the months. I have a slicer with the months and if I select January and February it should show the actuals from those two months and the rest should be the budget information.

I followed a video from SQLBI and I got a measure that works, but the problem I have is that the measure is based on the assumption that either budget is empty or actuals is empty. That is not always the case. For example:

- there are no sales yet in March

- an invoice for rent for March has already been received in February and costs are assigned in March.

With the current measure I get the correct information for sales (no actuals so basiscally it's all budget in March. Housing costs however are the total sum of the actuals + budget.

Is there a way to use the slicer in this, so I can cutoff the period?

The formulas currently used are:

RF = 
Var LastDateWithSales = calculate(max(Transacties[Rapportagedatum]), REMOVEFILTERS())
Var RemaingForecast = 
    CALCULATE(
        [Begroting],
        keepfilters(DimDate[Date]>=LastDateWithSales)
    )
Return 
    RemaingForecast

Sales + forecast = [W&V]+[RF]

[amitchandak]

@lekkerbek , You can add control, like this example

RF =
Var LastDateWithSales = calculate(max(Transacties[Rapportagedatum]), REMOVEFILTERS())
Var RemaingForecast =
CALCULATE(
[Begroting],
filter(all(DimDate) DimDate[Date]>=LastDateWithSales && DimDate[Date] <= eomonth(LastDateWithSales,3) )
)
Return
RemaingForecast