Results 1 to 5 of 5

Thread: What's the best calendar strategy?

  1. #1
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365

    What's the best calendar strategy?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •