Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |