By Audrey Gerred

Fun Fact… I used to be intimidated by creating a well-defined custom Date table in Power BI, heck, I used to be intimidated about creating a date table period. The default Date tables were getting the job done (for the most part), so it’s definitely possible (i.e. totally happened) that years ago, my early models didn’t have a date table, just date fields and the default Date tables that came with them.

Thankfully, those days are long behind me! Did I figure out how to craft a beautifully designed, written, formatted, well-defined date table that accommodates a Gregorian Calendar, Fiscal Calendal, and/or Fiscal Weekly Calendar all in one, has practically every dimension on a date that you could want, makes time-intelligence measures an absolute walk in the park at so many levels of granularity, and can easily be enhanced if needed, then generously share it out for the world to use? No! I definitely did not do that!! But, have no fear! Marco Russo over at SQLBI does have such a wonderous unicorn! Seriously, SCORE! I legitimately LOVE this Reference Date Table.

Yes, I will die on that hill, but I doubt I will have to! Read the article, download the file, get familiar with it… I promise you will love it (and, if you are anything like me, you will not only stop dreading or foregoing the custom Date table, you truly appreciate the versatility it gives to your modeling and visualizations that you will be inspired to blog about it!).

If you didn’t know about custom Date tables, didn’t know why custom date tables are so versatile in Power BI, didn’t care about custom Date tables, you think I’m going a little over-board about a custom Date table (especially when Power BI does work behind-the-scenes on fields it recognizes as a date field and creates default Date tables which can easily be used for time intelligence) and you want to see what made me such a big advocate of using custom Date tables as a best practice, or didn’t know about this specific custom Date table… come on and Power BI with Me!

Time-Intelligence metrics are often used as KPIs and it’s important to ensure you can do the measures that are needed… sometimes you need to account for a 4-4-5 calendar utilizing fiscal weeks, maybe your business week runs Wednesday to Tuesday, you want to analyze work done on holidays, you follow a fiscal calendar, or any other number of scenarios and levels of granularity you may want to look at are handled with ease with this custom Date table. With a custom Date table, you can have access to many dimensions at your finger-tips to quickly filter on or utilize in a measure.

By default, when Power BI recognizes a field as a date, it creates the default Date table in the background… it does this for every date field that exists in your model. Adding a custom Date table does not automatically turn the default tables off, so you will have to right click on the custom Date table and mark it as the Date table. Doing this will disable the default tables so memory is not being wasted on them. If you are not utilizing a custom Date table marked as a Date table, you should remove any date field you are not going to be utilizing to minimize on the hidden tables in the background.

That’s it… it’s as easy as heading to SQLBI and integrating this work of art into your modeling. Thank you, SQLBI!