Problems creating Calendar in Data Model

[FlowSnake]

Dear all,

Just now I've started in Power Query/Pivot, and using the Data Model. It's a great tool, and mostly I'm learning and applying a lot.

However, I've been running into an issue where the automatic Calendar function in the Data Model keeps relating to wrong dates, i.e.; my data starts at 1-1-2018, but the Calendar starts all the way back in 1-1-0209, listing all days to 2020.

I've tried messing around with data types in Power Query, Locale Date notations, Cultures, etc. But nothing helped to prevent this from happening. In a clean sheet with only a Query with dates, it does work.

Now I removed all tables from the Data Model, and only included the extracted Query dates, and it loads perfectly, after which I loaded all the other tables succesfully as well.

Now my question is, why? Does it matter what tables I've loaded into the Data Model in what order, since there's no way to 'select' the source for the Calendar. Is this a simple lesson for future reference to first load the data table for the Calendar, and then add all other tables?

I hope to get this cleared up.

[Olly]

Auto calendar will base the date range on all Date type values in your data model. If the auto calendar is creating rows outside your expected range, it would indicate that there are date values outside your expected range in a date column somewhere...

[FlowSnake]

Hi Olly,

Thanks for your quick reply.

That might explain the issues I've been running into since I loaded a table containing information about our entire product range, which includes fields when the article has been introduced, when to start/stop purchasing, and start/stop selling.

So I suppose my fix for now works, untill the entire data model is refreshed. I will delete these columns from my data set in Power Query instead since they're of no use anyway.

Thanks again!

[Olly]

You don't have to use the auto Calendar, you could create your own Calendar table, with manual control of the date range. Here's an example, using Power Query:

Please Login or Register  to view this content.

[FlowSnake]

Ho, that's a nice one. I will try that one as well, thanks. Will I have to use the 'mark as date table' in power pivot using that query?

[Olly]

Yes, mark as date table so that you can use all the time intelligence functions.