locked
1900 coming up as value in slicer RRS feed

  • Question

  • Hello. I'm slicing value on years and 1900 is coming up, even though when I put year and a value on columns, 1900 does not show up...just the values that I would expect. Any ideas to troubleshoot? Thanks. 
    Wednesday, November 9, 2016 12:34 AM

Answers

  • Hallo. It ended up being an error in my date table inside excel. Oopsy-daisy!!
    • Marked as answer by AlexMartini Tuesday, November 15, 2016 12:00 AM
    Tuesday, November 15, 2016 12:00 AM

All replies

  • Hello Alex,

    Does your source data contain the numeric value 0? Excel converts this to date value "00.01.1900", which can be the reason for year 1900.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 9, 2016 9:05 AM
  • Actually, the value 0 translates to 30-12-1899, and every value between 2 and 366 is the year 1900.

    You probably don't have any real results on these dates, that's why the year 1900 doesn't show up in pivot tables or charts.

    Wednesday, November 9, 2016 1:19 PM
    Answerer
  • Michiel, which MS Excel version are you using? In MS Excel 2010 & 2013 I get 1/0/1900 for numeric 0:


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 9, 2016 5:21 PM
  • Hi Olaf, you are right about Excel, but I'm using Power Pivot here which handles dates a bit differently.

    Interesting difference, by the way - somewhere a translation has to be made. And what is 1/0/1900 anyway ;-)

    Thursday, November 10, 2016 7:32 AM
    Answerer
  • Hi AlexMartini,

    There is a slicer which includes year, right? If it is, is the year field value from the year column in the date model? How do you show up the year and values, is there some calculation? Please share more details, and post the sample date and error result screenshot for further analysis?  If the 1900 value is located in year column,  it can be shown in anywhere of power pivot table.

    In addition,  as the official document described, for some reason PowerPivot will not create a Date Table for dates before March 1, 1900. if you try to use a table that starts with Jan 1 1900 you will get an error. I suspect that it is an issue with leap year treatment for 1900.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 10, 2016 8:51 AM
  • Hallo. It ended up being an error in my date table inside excel. Oopsy-daisy!!
    • Marked as answer by AlexMartini Tuesday, November 15, 2016 12:00 AM
    Tuesday, November 15, 2016 12:00 AM