Forum Discussion
Weight values over time
You could do it by:
- Creating an attendance table so there would be 1 row for each rep per month (you could go down to day level if you have the data)
- Each month append a new set of data for reps still with you
- Include a column with their "score" i.e number of months since start date/12 with a max value of 1.
Something like
Month|Rep_ID|Score
1|1|1
1|2|0.2
2|1|1
2|2|0.3
Then just aggregate the scores by months. This approach would also allow reps to have gaps in thier employment i.e. sickness etc.
Thanks, Itchy. The Attendance table is the part I'm struggling with. Any ideas on how to build that when all I have to start is the rep and the dates?
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.