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] ) )
)
)