Forum Discussion
Weight values over time
If its feasible for you then I think a stored procedure in your database may be neatest way to go.
A quick and dirty alternative would be to manually create a template table in excel prepopulated with data for all the months and reps you will need for past and future (100 months and 100 reps for example). Import it into the model and calulate the score column on the fly using your start and end dates. The template would just act as a framework to hang your real data (start/end dates) from.
If you link your newly created table to your reps table you can restrict results to just matched reps, all the other data would be ignored until a new rep was added that matched.
I've created a very basic test model - here
Seems to work, little bit messy as I created a series of calcluated columns to do the calculations
To extend you would just need to add new reps to reps table and an attendance block to the attendance table.
I don't normally like taking this kind of approach as the template attendance table is finite and would have to be updated at some point in the future if your number of reps got beyond a certain point, it should solve the problem in the short/medium term though until somone better with DAX comes along and figures out a much neater solution.