Sharing my Power BI template: Calendar Query aka Date Table

A frequent question in our Power BI User Group meetings is about using a Date table in Power BI and how to create one. Use this template to create a query named “Calendar” that includes your fiscal year dimensions. Follow the instructions in the pdf to set up your workbook for successful time series analysis.

There are many resources for Date tables online, some in M Query, some in DAX. But I have struggled to find a plug-and-play solution with a dynamic date range.

This example dynamically calculates the start date of the Calendar dimension based on the first day of the earliest fiscal year found in your Gifts[date] (or other field of your choice). It dynamically calculates the end date to be the last day of the current fiscal year.

Required connections:

The M Query code in the CalendarQuery.txt file can be applied to any data source, but if you are an RENXT user who has the Power BI Connector set up already, you can also use the .pbit file to apply to your own data.

Suggested skill level: Beginner

Configuration details: This .pbit (template) file should connect immediately to your installed Power BI connector, populating with your NXT data. Note: this template queries Gifts multiple times, which can take a long time on large data sets. The .pbit file is provided for your convenience, but is not required as you can follow the instructions to copy/paste script from the file CalendarQuery.txt.

If query run time becomes prohibitive, please see the attached pdf for alternatives to set-up start date without querying Gifts[date].