Dynamically Actuals up to last month and forecast from this month forward

[Tiddlerontheroo]

Hi there,
I am trying to build a dynamic measure in DAX that uses an "Actuals" measure for all historical months up to the last completed month, and uses a "Forecast" measure for the current month forwards. I'm currently using TODAY() as the date, which is therefore pulling through some actuals data for the current month (September in the example below) and adding it to the September forecast, therefore overstating the "Actuals & Forecast" value for the current month. In my simple example below, the September Act&Fcast should be $23 (i.e. ignoring the partial actuals month of $4) and only using the forecasted $23, but my DAX is giving the answer of $27, being the sum of the Actuals ($4) and Forecast ($23) for the current month of September.

Here's the current DAX:

Act&Forecast  =

VAR LastMth =

CALCULATE(

    TODAY(),

    REMOVEFILTERS()

)

VAR Actuals =  CALCULATE([Actuals])

VAR FutureForecast =

CALCULATE([Forecast)],

FILTER(_Calendar,

_Calendar[Date]> LastMth

)

)

VAR Result = FutureForecast + Actuals

RETURN

Result

			Should Say	Is Currently Saying
	Actuals	Forecast	Act&Fcast
Jan	$23		$23	$23
Feb	$21		$21	$21
Mar	$22		$22	$22
Apr	$24		$24	$24
May	$26		$26	$26
Jun	$28		$28	$28
Jul	$21		$21	$21
Aug	$21		$21	$21
Sep	$4	$23	$23	$27
Oct		$24	$24	$24
Nov		$24	$24	$24
Dec		$25	$25	$25

@moizsherwani 😉

[UsmanBasha]

Hi, I would be very helpful if you help me with the dax formula for Forcast column. Thank you.

[tamerj1]

Hi @Tiddlerontheroo 
Please follow my proposed solution for this How to frame L3M moving average logic for the futu... - Microsoft Power BI Community

[v-tangjie-msft · Accepted solution]

Hi @Tiddlerontheroo ,

According to your description, if the value of Forecast column is empty, Act&Fcast column will output the value of Actuals column, if the value of Forecast column is not empty, Act&Fcast column will output the value of Forecast column.

Then you can create  a measure.

Act&Forecast  =

VAR LastMth =

CALCULATE(

    TODAY(),

    REMOVEFILTERS()

)

VAR Actuals =  CALCULATE([Actuals])

VAR FutureForecast =

CALCULATE([Forecast)],

FILTER(_Calendar,

_Calendar[Date]> LastMth
)
)

VAR Result = IF(FutureForecast <>BLANK(),FutureForecast,Actuals)

RETURN
Result

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.