Rolling estimate calculation


#1

Hi Sam,

I am trying to calculate a rolling 12 months estimate defined as follows in period t:

Period, t (actuals) + Sum(Period t+1…t+n (budget)) + Sum(Period t+n…t+11 (forecast))

Here’s an example.

In figure 1, I’ve tried to illustrate what the calculation should look like. As you see, it should always prioritize forecast over budget for any period with an overlap.

So the estimate calculation for EBITDA in february should sum period actuals (feb) + sum(period budget (mar-apr)) + sum(period forecast (may-jul)). Note that this is just a dummy dataset. In practice, a febraury estimate will probably be period actuals + remaining year budget + a forecast for january 2019.

Figure 1

In figure 2, you see the simple datamodel. The live version is bit more complex for follows the star-structure.

Figure 2

So far, I’ve calculated the core measures and a Full Year Forecast but I don’t have any ideas on how to handle the prioritization between forecast and budget.

Blockquote
Total:=SUM( factFinancials[Beløb] )
Total Actuals:=CALCULATE( [Total] ; factFinancials[Source] = “ACT” )
Total Budget:=CALCULATE( [Total]; factFinancials[Source] = “BUD”)
Total Forecast:=CALCULATE( [Total]; FactFinancials[Source] = “Forecast”)
FullYear Forecast:=CALCULATE( [Total Forecast] ; ALL( factFinancials[Source] ); DATESINPERIOD( dimDato[Dato]; FIRSTDATE( dimDato[Dato] ) ; 12; MONTH ) )

which results in figure 3.

Figure 3.

Crossing my fingers that you (or any one else) have a brilliant idea on how to approach this calculation.

Best regards
/A


#2

Note that in figure 1, the topic in column J returns: “Forecast” - this is of course the “Estimate” just to clarify any confusion.


#3

I’m try to get my mind around this one and don’t think it’s too difficult…in saying that I haven’t fully got the scenario.

Is there any reason you couldn’t just use a simple IF statement to prioritize one over the other on every row?

Something like this

IF( NOT(ISBLANK( Total Forcast )), Total Forcast, Total Budget ) )

You’re just looking to calculated the rolling 12 months calculation within the FullYearForecast?

Actually you probably need to add a bit more to this, but not too far away with the IF statement idea.

Check out this idea I run through here

Around the 6:11 min mark, I showcase the formula structure to use.

The idea is to place a virtual table within this formula using a combination of SUMX and SUMMARIZE. This is a really common pattern I utilize to answer things like this.

Can you review this and see if you can work in the logic you need to the formula pattern.

See how you go, if no luck working it out can you add a Power BI file example of the scenario and then I can work it up at my end.

Thanks