Dynamic measure based on linear calculation/ statistics

[Ritaf]

Hello all

We are trying to build dynamic yearly target for purchase managers by monthly percent economy from their purcheas deals $.

there is 2 ways to do it:

1. By linear calculation of monthly purchase of entire months * target percent + average monthly purchase of entire month*target percent.

i tried to calculate my purchase for all year with dax formula:

MonthlyDynamicPurchase = if(month(MAX(dimDate[date]))<=month(today())-1,[TotalPurchase$],CALCULATE(SUMX(factOrdersPurchase,[TotalPurchase])/DISTINCTCOUNT(dimDate[month]),ALLEXCEPT(dimDate,dimDate[year])))

unfortunately it is'nt working , please see an attached pict.

2 second way is to calculate percentaje of monthly purchase from yearly , by last 5 years , calculate average of them and use it for making "prediction" of monthly purchase for new months i tried to use formulas above and it isn't working two because the slicers's effect ,i will realy appriciate any assistance / quide to make it working.

the formulas i used:

 month_year_purchase =

var _currentyear = YEAR( TODAY() )

RETURN

CALCULATE(

[TotalPurchase$],

'dimdate'[year] <> _currentyear

)

----------------------------------------

complete_year_purchase =

var _currentyear = YEAR( TODAY())

RETURN

SWITCH(

TRUE(),

SELECTEDVALUE(dimDate[year])= _currentyear,

BLANK(),

CALCULATE(

[totalpurchase$],

all(dimDate),

VALUES(dimDate[year]),

dimDate[year] <> _CurrentYear

)

----------------------------------------

Percent_of_yearly_Purchase =

AVERAGEX (

VALUES ( dimDate[MonhYear]),

CALCULATE (

DIVIDE ([month_year_purchase], [complete_year_purchase] ),

ALLSELECTED ( dimDate[year] )

)

)

)