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

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

Calendar Table

S

Swati Vishwanathan

New Member
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
 
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
 
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...
 
You must log in or register to reply here.
Back
Top