|Q: How can I create Fiscal Date/Time table for PowerPivot if I do not have any source for that?|
|Written by PowerPivot-Info.com|
|Tuesday, 12 January 2010 23:49|
Q: How can I create Fiscal Date/Time table for PowerPivot if I do not have any source for that and my Fiscal Year starts not in January? I would like to have flexibility in how date fields are calculated.
Majority of PowerPivot application will have date type fields (sometimes called Time) that will be used to build different reports. If you are lucky, then one of your source databases will have a date table that will contain all date attributes. But if you do not have source for your date table, then it is quite easy to create Excel table with our date values and then add it as a linked table to your PowerPivot application.
To build your date table you will need to create one column in excel that will have all your date values. You can do this by:
Now it is time to create Excel calculations for parts of the date: week, month, quarter and year. For that just enter in the cells B2, C2, D2... names of the column and then enter in the cells B3, C3, D3, ... formulas from the list below.
List below contains formulas for different calculations. Most likely for your implementation you will want to use just some of the columns. I provided here extended list so you would have something to choose from.
* - We prefixed month names with month number. This way month names will be properly ordered in the list
I would like to point out that all these formulas are NOT DAX formulas - they are just simple Excel formulas.
After you added these formulas to your columns, you should copy all formulas down to each row. This way you will calculate columns for all rows. Here is how your Excel sheet will look:
Now you are ready to convert your Excel spreadsheet to table. For that select any cell in your spreadsheet area that contains date values, then select menu item "Insert" and then "Table". New dialog "Create Table" will pop up where you will be able to specify area that represents your table. In the same dialog you also will be able to specify paramter "My Table has headers" value.
After you will click "OK", data on your Excel sheet will be converted to the table.
To load this new data table into PowerPivot all you have to do is select menu "PowerPivot" and then click on the button "Create Linked Table". After that do not forget to join your date table to your other table.
Are you using different calculations for your PowerPivot implementations? Please share them with community by posting your calculations here in the comments. Thank you for that!
- PowerPivot / PowerView Excel Workbook Optimizer
- DAX Studio - Free Excel Add-in
- How to solve issue when PowerPivot for Excel crashes on all workbooks
- Using PowerPivot to analyze web access log in "Combined Log" format (CLF)
- Q: I have integer amount that represents duration in seconds. How can show this amount in format "h:mm:ss"?
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of suggested datasets to test PowerPivot
- How to install PowerPivot for Excel and list of know issues
- List of PowerPivot DAX functions with description
- Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction
- Learning PowerPivot and DAX
- List of PowerPivot DAX functions (short)
- PowerPivot's impact on BI pros?