Introduction
Currently experimenting with PowerPivot and I needed a date dimension in PowerPivot. I used to create this in SQL Server but I decided to build this in Excel. On BlueGraniteAcademy there is a splendid example on how to create this. I just followed this example and below you can see my implementation of this.
Date dimension in Excel
So, In order to use a date dimension in Powerpivot we have to use a source for this. In this case I would like to use Excel for this. Why, beause it's easy to create in Excel, you can keep all your data together with the other data in excel and I needed it for some demos.
We need to create to the following table:
We have a couple of columns:
- Datekey : =YEAR(B2) * 10000 + MONTH(B2)* 100 + DAY(B2)
- DataValue : fixedvalue eg 1/1/2012
- DayOfMonth : =DAY(B2)
- DayOfYear : =B2-DATE(YEAR(B2), 1,0)
- Year : =YEAR(B2)
- MonthOfYear : =MONTH(B2)
- MonthName : =TEXT(B2, "mmmm")
- QuarterOfYear : =INT((MONTH(B2)-1)/3)+1
- QuarterName : ="Q" & H2
The next step is to drag the line downwards until the point you want to reach. In my case 31/12/2015.
The next thing is to select the complete table of the date dimension and press CTRL - T and the followig window appears.
Press OK and rename the table by clicking on Table Tools Design and editing the TableName Field
Go to the PowerPivot Tab and press on Add to Data Model. PowerPivot Should look like this:
Conclusion
This is a simple example of creating a DateDimension for PowerPivot. Perhaps in the future I'll add more fields to this implementation.
Greetz,
Hennie
