Weight values over time

[joeort]

Has anybody figured out how to do this?

I want to weight the number of sales reps we have based on the length of time they've been selling for us.  I have a ramping schedule similar to the simple example below (this would say a rep is fully-ramped at month 12):

Month 1: 1/12

Month 2: 2/12

Month 3: 3/12

Month 4: 4/12

Month 5: 5/12

Month 6: 6/12

Month 7: 7/12

Month 8: 8/12

Month 9: 9/12

Month 10: 10/12

Month 11: 11/12

Month 12: 12/12

Month 13: 1

Month 14: 1
...

I need to do some calculations based on a table of their names and start/end dates as reps:

Rep Name | Start Date | End Date

Johnny | 1/1/14 | Null (Still selling for us)

Billy | 1/1/14 | 6/30/15

Jimmy | 4/1/15 | Null

Craig | 12/1/15 | Null

This list is coming from a connection so as we add new reps, the table will continue to expand.  

I want to know how many weighted reps we have in any given month.  Based on the 4 sample reps above, I would have:

2/12 of a rep for January 2014

4/12 of a rep for Feb 2014

...

2 1/12 reps for April 2015

2 2/12 reps for May 2015

...

Any ideas on how this can be written in measures/columns?

[joeort]

Bump

[itchyeyeballs]

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.

[joeort]

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?

[itchyeyeballs]

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. 

[itchyeyeballs]

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.