Measure to convert values to constant dollars over time?

[MojoGene]

I am trying to show the effects of inflation on values. In this case, the values are billing rates. I have a measure that calculates the average billing rate as follows:

Average Hourly Rate = SUM(Timeslips[Hourly Value])/SUM(Timeslips[Hours])

 I also have a table of inflation data from the Bureau of Labor Statistics for the years 2000 to 2016 (Base Period 1982-84=100):

Year	Annual	ANNUAL%	CUMULATIVE%	INDEX
2000	250.9	0.00%	0.00%	      1
2001	260.9	3.99%	3.99%	      1.039856517
2002	268.0	2.72%	6.71%	      1.068154643
2003	272.6	1.72%	8.42%	      1.086488641
2004	281.9	3.41%	11.84%	      1.123555201
2005	288.6	2.38%	14.21%	      1.150259067
2006	296.4	2.70%	16.91%	      1.18134715
2007	308.205	3.98%	20.90%	      1.228397768
2008	317.124	2.89%	23.79%	      1.263945795
2009	322.814	1.79%	25.59%	      1.286624153
2010	330.185	2.28%	27.87%	      1.316002391
2011	336.862	2.02%	29.89%	      1.342614587
2012	341.978	1.52%	31.41%	      1.363005181
2013	349.794	2.29%	33.70%	      1.394157035
2014	353.325	1.01%	34.70%	      1.408230371
2015	362.759	2.67%	37.37%	      1.445831008
2016	371.484	2.41%	39.78%	      1.480605819

Both the Timeslips table and the BLS Data Table have a relationship to a Date table in my data model.

So, I would like to factor the annual average billing rates by the inflation data to show constant dollars. E.g., a billing rate of $100 per hour in 2000 would be equal to a 2016 billing rate of 100/1.480605819=67.53 per hour.

While the math is simple, I am stumped on how to develop a measure to show this. Any help would be appreciated.

[danrmcallister · Accepted solution]

@MojoGene I might need an example of your hourly data to better understand what you're trying to get at, but I set up the below along with an overly simplified aggregate dataset.  I didn't bother making a date table since my table was aggregated, but the concepts should be the same - not sure until I see it.  Anyway, I just made an additional measure based on your measure:

Average Rate Infl Adj = [Average Hourly Rate] / AVERAGE(BLSData[INDEX]) 

If you give me a little more demo info I'll try to get a better answer.

Dan

[MojoGene]

Dan:

Thanks very much for the input. Your solution works as I intended. I was actually encountering problems because PBI Desktop was bringing in the BLS data as text and the date relationship was not being picked up correctly. It took me a while to get that straightened out, but once I did I was able to follow your explanation very clearly.

Thanks.