cancel
Showing results for
Did you mean:
Helper III

## Rolling forecast cutoff date based on slicer

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]``

Super User

@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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!