Forum Discussion

Foxxon28's avatar
Foxxon28
Helper I
3 years ago

Create Forecast Measure

Dear reader,

 

I want to create a Power BI line chart that shows a running total of the total Budget, per month, and fill in the rest of the year with an average of the forecast.

 

My data structure is as follows: 


The Actual And Budget column are updated monthy, whereas the Forecast columns are usually the same for each month, as they show the (FY = Full Year) total expected budget.

 

What I have done so far is: 

VAR _MaxRunning = MAXX('Costs', [Running Budget])
VAR _MaxBudget  = MAXX('Costs, 'Costs'[Budget FY])
VAR _DateDif    = 12 - MONTH(MAX('Date'[Date]))  

RETURN
DIVIDE(_MaxBudget - _MaxRunning, _DateDif)

This seems to calculate the remaining monthly budget, but it only shows it for the months where I have data (januari / april), whereas I want it to show a line from may - december.

 
The [Running Budget] Measure is as follows:
Running Budget =
IF (
ISBLANK ( SUM ( 'Costs'[Budget] ) ),
BLANK (),
CALCULATE (
SUM ( 'Costs'[Budget] ),
FILTER (
ALL ( 'Costs' ),
'Costs'[Date] <= MAX ( Date[Date] )
)
)
)
 
 
Would love to hear your ideas! 🙂
Daniël

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

    • Foxxon28's avatar
      Foxxon28
      Helper I
      3 years ago

      Thanks for the suggestion!

      However I do not think this will work, as I am working with a variable "Actual Budget", that gets updated every month and need to "fill in the blanks, till December". The method used above does a "fill in the blanks" for the whole year, whereas I need to have a running total of the "known data" (From my example jan - apr) and fill may - dec using a measure, based on a known Forecast Budget (135).

       

      What my expected outcome would be to show a running total from jan - apr (going to 53)

      and have may - dec be filled with a liniar line, straight to 135.