Sharing my Power BI template: Calendar Query aka Date Table

Options
Rebecca Sundquist
Rebecca Sundquist
Seventh Anniversary Kudos 2 Name Dropper Participant
in SKY Developer General Discussions

One of the first questions that comes up 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].

Calendar Query.pdf

CalendarQuery.zip

Tagged:

Comments

  • Rebecca Sundquist
    Rebecca Sundquist
    Seventh Anniversary Kudos 2 Name Dropper Participant

    Here is an excellent walk through for using the DAX expression CALENDARAUTO() to create a calculated table for the date dimension.

    Create calculated columns - Training | Microsoft Learn

    CALENDARAUTO() identifies the minimum and maximum date value across all of your imported tables. Note that DAX creates a calculated table in Power BI Desktop, which is different from the template above, which uses power query language (M Code) in Power Query Editor.

Categories