Hi Fellow Power BI Users -
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