1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calendar Table

Discussion in 'Power Pivot, Power Map etc' started by Swati Vishwanathan, Jun 2, 2017.

  1. Swati Vishwanathan

    Swati Vishwanathan New Member

    Messages:
    20
    Hello
    I want to have a list of dates which I can use to count cases opened and closed in a particular period. I want to do this in a pivot table so I dont want to use my opened date or closed date as dimension. I want to use an independent list of dates for this.

    When I try to create a calendar table in power pivot so I can link it to my case data, I keep getting an error that it is not formatted as date or contains duplicate values.

    I dont see any duplicate values...all are unique dates and it is also formatted as date...

    Please can anyone assist me



    upload_2017-6-2_10-51-34.png



    upload_2017-6-2_10-52-17.png
    Regards
    Swati
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
    Hi ,

    Can you upload your workbook with the dates table in it ?

    Narayan
  3. Swati Vishwanathan

    Swati Vishwanathan New Member

    Messages:
    20
    Hello Narayan

    Thanks for taking time to help me understand this. I was able to use the calendar table to work out my backlog table. However when I add another table and try to connect it to my calendar table, the relationship breaks. I cannot understand why because I can see that I have linked the date on calendar table to date in my new table as well

    Essentially I have one calendar table and two fact tables. The calendar table works fine with one table but breaks with the other I cannot understand why

    I am just wondering if it is because date contains timestamp and even when I format it as date, it continues to incompatible to the dates in my calendar table

    How can I remove timestamp from date within powerpivot, other than playing with formatting

    I used the INT function but it returns an integer in powerpivot and doesna allow me to format that as date

    Regards
    Swati
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
  5. Swati Vishwanathan

    Swati Vishwanathan New Member

    Messages:
    20
    Thanks Narayan

    Those links were very helpful. It was really an issue with the way my dates carried date stamp, once I cleaned it, I was able to link all the tables

    Thanks again...

Share This Page