locked
PowerPivot date issues - Help! RRS feed

  • Question

  • Hi all,

    I'm getting an annoying issue with date formatting with PowerPivot and Excel. When I create a pivot from the PowerPivot workbook itself, the dates are correct in UK format. However, when I access the PowerPivot model as an OLAP cube stored in Sharepoint, the dates appear as US format.

    Has anyone come across this issue before? And.. more importantly, does anyone have a solution?

    Thanks in advance :)
    Monday, October 20, 2014 10:10 AM

Answers

  • When accessing any OLAP source, Excel will interpret rowfilter fields as text. Try some slicers or a timeline for filtering dates.
    • Proposed as answer by Michael Amadi Wednesday, October 22, 2014 10:26 AM
    • Marked as answer by Charlie Liao Wednesday, October 29, 2014 3:26 AM
    Tuesday, October 21, 2014 2:51 PM

All replies

  • http://cwebbbi.wordpress.com/2013/03/21/ukus-date-format-bug-in-powerpivot-and-ssas-tabular/

    T-SQL Articles
    T-SQL e-book by TechNet Wiki Community
    T-SQL blog

    • Proposed as answer by Michael Amadi Wednesday, October 22, 2014 10:26 AM
    Monday, October 20, 2014 10:15 AM
    Answerer
  • Great! Thanks for that, I now can see dates in UK format.

    However, I still have an issue with the the Excel PivotTable not treating dates as dates (i.e. when I right click the date, I don't see 'Date Filters')

    I have 2 tables, TableA is marked as a date table, and TableB is a data table with a few date columns in it. TableA's date works and I see 'Date Filters', but the same doesn't happen on any date in TableB. It's almost as if it is treating all the dates (even though they are formatted as dates in the model) as General text.

    Hope this makes sense. Any ideas?

    Tuesday, October 21, 2014 12:59 PM
  • When accessing any OLAP source, Excel will interpret rowfilter fields as text. Try some slicers or a timeline for filtering dates.
    • Proposed as answer by Michael Amadi Wednesday, October 22, 2014 10:26 AM
    • Marked as answer by Charlie Liao Wednesday, October 29, 2014 3:26 AM
    Tuesday, October 21, 2014 2:51 PM