Create Forecast Measure

[Foxxon28]

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

[amitchandak]

@Foxxon28 , refer if this approach can help

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[Foxxon28]

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.