Weekly membership level calculation
We do a friendly competition between customer care teams. the calculations up until this point have been calculated on a monthly basis but were calculated incorreclty due to membership attribution. We allow training to reduce the divisor for points scoring. So, if a rep has been here less than 60 days, they are at a member ship level of 0. After that, they move up to a membership level of 0.5. On the 90th day, they have a membership level of 1. The numerator is a calculation based upon performance value including calls, orders, and invoices. We call that calculation [Attribution Amount]. My problem is getting the calculation to correctly attibute membership and correctly roll up the numbers. The actual calculation is Performance Average = DIVIDE([Attribution Amount], [Membership]). when using this calculation, it applies the same membership divisor accross the totals instead of as it changes within the month. I would like to create a weekly calculation that can be summed up. I believe I may need to create a table showing the weekly membership number but I have lost my way. So far, I have added a column to the fact table showing the number of days since training began. Then I added another column showing membership status for the day the transaction occured. this give me the correct line level membership status. How can I use that level of granularity to calcuate points on a weekly basis and then roll it up into a monthly total?Dynamic measure based on linear calculation/ statistics
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 ] ) ) ) )
