Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lekkerbek
Helper IV
Helper IV

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]

 

1 REPLY 1
amitchandak
Super User
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.