Wrestling with time dimension in Power Pivot

[Chtorr00]

I am building a data model to teach myself PowerPivot, and have run into a conceptual roadblock for which I seek advice.


I have 10 year forecasts for the growth of every US occupation. From that, I am able to compute the CAGR for each occupations, and then generate a 20 year forecast for occupational growth. In conventional Excel, this would involve simply tweaking the CAGR formula in each column for two decades, and then drag the formula down. The obvious answer in Power Pivot would be to just create 20 calculated columns, however from everything I have read, it would be better for the model if I had time as an actual dimension. Right?


If that is the case, should I perform the extrapolation in Excel, and then use Power Query to unpivot that table into the data model? Or is there some other more DAX-sensible approach using a date table, and some wizardry with Measures?


There is a related issue that might have some bearing on the answer. The point of doing the 20 year forecasts is that I want to use two disconnected slicers to enable the ability to interact with the forecast using by altering the inputs from each slicer. One slicer uses a related variable on another lookup table, and is straightforward to implement. The other slicer is designed to vary the assumed adoption rate by 2035. To recap:
Slicer 1: Select a threshold, items above that threshold get passed to slicer 2.
Select adoption rate for 2035 (20%, 40%, 60%, 80%, 100%). This selection gets multiplied against slicer 1, and then subtracted from the Sum of 2035.
I have this implemented in prototype form for the year 2035, and it seems to work fine. What I would like to do is infill years 2015 to 2034 with the "data" that leads up to the end result (for charting and visual purposes). My idea is that the selected adoption rates can also get expressed in the form of series of fixed CAGR over the period, so the incremental growth each year - for each adoption rate - can be calculated. So for example, the "80%" adoption rate could be expressed as a CAGR of -0.011095 and plugged into the CAGR equation for each year over the two decade forecast period. Conceptually, that is the method to my madness, but I am somewhat stumped as to how to implement it in DAX and PowerPivot.

I gather that I can separate the applied equation from the slicer label using a single parameter table.
Parameter Table – DAX Patterns


I can the sort of see how to brute force all these extrapolations and CAGRs into a heaping pile of measures but have to wonder if there is a better way to implement the second slicer, or the time dimension in general?


FYI, I am using PowerPivot in Excel 2013 (32bit), on a rinky-dink laptop. My prototype is only using a 1000 row dataset, but ultimately I would like to apply it to a 150K row dataset.

Thanks,

Mark

 

[Matt Allington]

There is a lot in your question. Let me give you some general advice. You are correct that 20 calc columns is the wrong approach. The trick with making the leap to Power Pivot from regular Excel is to learn to think in tables rather than sheets. One way to do this is to always set up a pivot table that displays your data and then click in the pivot table to write your measures. You should be thinking "what column/measure do I want to add to this pivot table to advance me towards my end goal?" So set up a clean pivot table so you can "see" the data you need to complete the next step. Then write a measure that completes that step. Think first in English, then convert to DAX. Hard code your measures with the variables first, then come back and add the disconnected slicer construct once it is all working.

 

[Chtorr00]

Thanks for the answer Matt, that is helpful.

Let me reframe by simplifying my question. Most of the data I am interested in has data that is aggregated by year. Most of the time, that data is coming out of Excel where it is reported by column. Is it generally better to unpivot that data and move it into a single Year column before you import it into your data model? I am unclear about what the disadvantage is to just leaving that data in the original format.

 

[Matt Allington]

Of course it depends what format it is in before you start. Getting the shape of your starting data right is a very important first step in data modelling win Power Pivot. The "out of the box" shape from a relational DB is rarely (maybe never) the right shape. If the data comes from a data warehous, then it might be close or even correct.

Read ad my knowledge base article about data shape here. The Optimal Shape for Power Pivot Data -
there is also an article about calendars in the knowledge base.

The difficulty in giving you advice is that some things are not clear. In the OP you state you have 10 year forecasts for the growth of every occupation. But you don't say what the data shape is. "growth" normally means % figures, but that would not be helpful/useful. If instead you have the forecast employment levels by occupation by year, then that would be good. Something like this (data shape of your table)

year.   Occupation.    ForecastQtyEmp
2005.    BA.                100,000
2006.    BA.                105,000

etc

so I am now thinking that you do indeed have % figures (presumably with a starting number - not sure). If this is the case, I would convert to absolute employment numbers by occupation by year before loading into power pivot. Use either Excel or Power Query.

If if you load a sample of the data, I could give you more specific advice.

Ps. My experience with students is that it is very difficult to successfully teach yourself Power Pivot unaided. I am sure you would make much faster progress and less mistakes with a book. Http://xbi.com.au/learndax