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.
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.
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.
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.