What's the best calendar strategy?

[Charley]

What's the best strategy for managing a dynamic calendar for DAX, as defined by Power Query?

We could keep it in the Data Model, in a worksheet linked to its Data Model, in a CSV file, in a separate workbook from which Power Query could import the table's data, or probably in several other configurations.

Which approach seems to work best? And why?

[Ken Puls]

Hi Charley,

Sorry for the late reply on this one. Personally, I load it via Power Query to the Data Model. This allows me to dynamically create the calendar to span the boundaries I need (full fiscal years), and have a date dimension for my model. (Necessary for correct linkage/slicing.)

[Charley]

Hey, Ken!

My thought was that we don't need to rebuild the calendar table each time we refresh our data. I assumed I could avoid that performance hit by building the calendar for the entire year, which means that I'd only need to refresh it yearly.

Hmmm...Something just occurred to me. I could set the build-calendar query up as a function, and call it only when a value is TRUE in a cell. That way, I could just switch the cell to TRUE, run my query when needed, and then turn it to FALSE again.

Charley

[Ken Puls]

I don't think that will work Charley... the table will be dependent on the True/False flag, so if you leave it False, won't it return you an empty calendar?

Personally, I've not found the performance hit too bad for the Calendar itself, but it depends on the method you use. Usually my facts and dimension tables are where the major processing happens, so I've never worried about the calendar overhead, as it's only a 2-3 second impact on the overall process.

[Charley]

Ken,

You're right. When I wrote that, I was thinking about Excel Tables, not Power Pivot. As soon as I started to think about PP, I realized the problem, of course.

I DO have a performance hit from the Calendar query, probably because I'm including a bunch of columns that seem like a good idea, but I haven't used yet. For example, in addition to a DayIndex, I've got a WeekIndex, MonthIndex, and QuarterIndex.

So I think I'll stay with what I'm doing now: I'm using PQ to maintain an Excel Table in a separate workbook. Then, in PP workbooks, I use a simple PQ query to load it quickly. (I use a simple fGetCellValue function to grab the full path to the calendar workbook from a named cell in each PP workbook.)

One advantage to maintaining a separate Calendar workbook is that I can add additional columns to the Table easily, using either PQ or worksheet formulas, whichever would be easier at the time. Another advantage is that I can maintain one Table that any number of PP workbooks can load.

Thanks!

Charley