Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016

Download Excel Files:

Learn how to: create Automatic Calendar Table in Data Model, New in Excel 2016. This ability was not in earlier versions of Excel.

[jazzista]

MIke.... great video as always.. love the auto-calendar feature. Major improvement in Power pivot 2016. Thanks for all the hard work and your excellent explanations.

[DougHExcel]

Sweet! Glad this feature made it into Excel 2016. Thanks for the video Mike.

[dhruvbhatt]

If I want Exel data with date and events to auto populate in calendar under appropriate month and date. Upto max of 5 events per day. How do I do that?

[vijayakumarsempungovindara]

Hi MIke, I tried it and it didn't work out. I made a relationship between Baseline date and calendar date. In Pivot, If I select the filter or any related to Calendar then it shows the month names in row labels but if I select the table which has data the pivot does not show the row labels.


Do the date column need to maintain any format? I tried both the calendar date and Baseline date in my data maintain the same format but didn't work out

[abdulsamadibrahim]

Thanks for this tip. But please, what if we need to create the fiscal quarter and fiscal period columns, does that mean we have to do it manually like the previous video? Thanks.

[rlinseva]

Thank you so much Mike! Your Channel helps me a lot; you honed my skills and understanding of excel. Bravo! Question: Can I add Fiscal Year and FY Month Number columns to the existing date dimension? If so, what is the proper formula(s) to add on each columns? I currently have the automatic calendar dates. thank you again!

[ijjohenry]

Dear Mike! Thank you very much for the great video. Keep it up. Bravo once again

[mattschoular]

That's great. It makes me ask myself why do I write complicated Access queries to aggregate by year etc...I'd be better to just use Access for data storage and use Power Query and Power Pivot for all my reporting needs. Thanks Mike

[zaidnawali]

hi mike new date table option is not highlighted only set default option is highlighted

[hemyone]

Hi. thank you for the amazing video. The data table button is disabled in my excel when I put my database and set up data table. How Can I fix this?

[robertgrlic]

Excellent video! Can I link a column that is part of a table (Calendar) in a data model, back into Excel for the purposes of creating a drop-down list that contains the values in said column? Is this even possible?

[ExcelWithChris]

Just got home and tried this. Works except. When one of my columns is the name of the day, and I use it as a slicer, it's in an alphabetic order. I added another column in the query for day number, then sorted that and was hoping the slicer will now pick up the new order of the day names, but no. I know an option is using a custom list to sort. The question I have is if I do that, it uses the custom list on my computer's Excel and I am worried if I upload it to a SharePoint server, the custom list will not "go with the file". Any suggestions?

[danish]

Awesome Video,
Is the same feature available in Power Query as well ?

[robertgrlic]

Good video! How come IsoWeeknum is missing? I can use it in a Excel sheet but in the power pivot calendar...isoweeknum missing?

[Chamchijjige]

Thank you very much sir. Trying to eat an elephant: a bite at a time.

[carlmov]

Hi, great tutorials. you are really an excellent teacher. question: how can I contact you, i need to know how to enter data in a calendar that changes as i change the month! thanks

[mariannehislop]

I have Excel 2019 for MAC and found the Design tab, but it doesn't have the Date Table. In fact I don't have any of the options. Instead I have Add Chart Element, Quick Layout, Change Colour, Select Data, Change Chart Type, Move Chart. And they are all greyed out. Has it. moved?

[valmin]

I can't find this in excel 2019, is it still possible? I have a Mac