다음을 통해 공유

Handling Dates in PowerPivot

  • 아티클

Recently a former colleague contacted me on how to format dates in PowerPivot. She had a date column in a PowerPivot workbook that she wanted to show on the row axis in a PivotTable.

datepowerpivot

However no matter how she formatted the column either in the PowerPivot window or in the PivotTable it always showed as a string in the PivotTable.

datepivottable

As described before by others the solution to this is to format the value correct using DAX in a calculated column, however a better way would be to create a time dimension. I have created a separate worksheet that I use in all my PowerPivot workbooks as the date dimension.
It contains a column in each format that I normally use when analyzing data based on dates. I have used normal Excel formulas to create strings that looks nice and can be used in either PivotTables or Slicers.

datedimension

 

I have included the Excel file for you to use if you would like to since this is a request that I have got from several customers.

dimdate.zip

Comments

  • Anonymous
    November 21, 2015
    Very helpful.  Will be a time saver for me.  Thanks much!