DAX for calculation of yearly forecast

[Anonymous]

I have a fact table that contains the forecasted amount for each initiative. This forecast is done at the start of the year so the value for the forecast appears only in the month of January in the fact table for each year.

The fact table also has the savings per initiative, Success Rate and Initiative Status            

Initiative Code	Period	Savings Actuals	Forecast	Success Rate	Reported	Initiative Status	Savings Validated	Indicator
SAV001	1/1/2022	80	100	1	80	Ongoing	70	P&L validated
SAV002	1/1/2022	50	90	1	50	Ongoing	45	P&L validated
SAV003	1/1/2022	100	100	.8	100	Ongoing	100	P&L validated
SAV001	2/1/2022	20	0	.6	20	Ongoing	15	Cashback validated
SAV004	1/1/2022	100	100	1	90	Closed	90	Cashback validated
SAV005	1/1/2022	60	70	.5	50	Closed	50	P&L validated

I need to calculate the value of forecast as modified using the following logic :

IF (Initiative Status ]='Closed' OR  THEN Forecast= Full_year[Reported].

ELSE Forecast = max (Full_year[Reported], [Forecast cell])

Forecast cell is the forecast value from the fact table.

Basis on the above calculation I need to calculate the Forecast Success Rate using the below formula:

IF (Initiative Status ]='Closed') THEN Forecast SR = Forecast ELSE Forecast SR = (Forecast - [Saving Validated])*[Success Rate] + [Saving Validated]

I have tried multiple ways using calculated column and measures but my forecast value is not calculated right. I am not sure which step I am missing.

My measure for forecast success rate is :

if(min(fact savings[INITIATIVE STATUS])="Closed",[Forecast],([Forecast Total]-[Savings validated])*min('FACT SAVINGS'[Success rate])+[Savings validated)).

Not sure though if this is the right approach or not. Since the savings are tracked on a daily/monthly basis. But my forecast is for the entire year.

[Anonymous]

Store values in variable and it will work

[Anonymous]

Thanks again but is it not same as 

if(min(fact savings[INITIATIVE STATUS])="Closed",[Forecast],([Forecast Total]-[Savings validated])*min('FACT SAVINGS'[Success rate])+[Savings validated)).

Sorry if my understanding is not right , but we cannot use the column with row context in a measure unless we use min, or max type functions.

Can you help me how to go about using variables.

[Anonymous · Accepted solution]

In my exemple, i ducplicate your field "inititive status" on a calculatedcolumn named "calculated inititive status" 

after I store the value of the calculated field in a varioble called statuscalc

here is the code

Calculated forecast =

var statuscalc = Feuil6[calculated initiativestatus]

var result=IF (

statuscalc = "Closed",

Feuil6[Forecast],

( ( Feuil6[Forecast] - Feuil6[Savings Validated] ) * Feuil6[Success Rate] ) + Feuil6[Savings Validated]

)

return

result

[Anonymous]

Hello

I hop eI understood well.

Is it what you need ?

Calculated forecast =

IF (

Feuil6[Initiative Status] = "Closed",

Feuil6[Forecast],

( ( Feuil6[Forecast] - Feuil6[Savings Validated] ) * Feuil6[Success Rate] ) + Feuil6[Savings Validated]

[Anonymous]

@Anonymous  thanks for your reply but I cannnot use the if condition in calculate as the [Initiative State] , [Sucess Rate] and [Savings validated] are calculated columns in the fact table.