Following on from a previous article (Creating A Date Dimension | DataHub Consulting) where we talk about the importance of a date dimension in a Power BI model. We have had very positive comments from the article and asked if we could expand the article to include the DAX and M query code example for creating a local date table in Power BI.

To recap, depending on your project and the data that you have available, then there three options for creating a date dimension. The recommended way would be to use the source system. That maybe a dedicated or serverless database using synapse analytics. There is also option to use Databricks to create a date dimension. If the organisation is using the new Microsoft Fabric, then under the data engineering and data warehouse option it also can be created. That said then there would need for data engineering skills required to achieve this.

But this is not always possible, so there are options to create the table either at runtime using DAX code, or when the Power BI model is being refreshed using M query language. Again, in the previous article we talk about creating a date table, and the pros and cons of each method. Also, the options and performance impacts of these.

Here’s a link to our previous article that discusses all of this in more detail. Creating A Date Dimension | DataHub Consulting

So that said let’s have a look at some code examples. Please note that these are examples allowing you to adapt for your individual requirements.

M Query Method

So firstly, the M query language, this will be used in the power query window. 

In the Power Query editor click New Source > Blank Query

Click the Advanced Editor to display the code in the editor. Replace the default code with the code in this article. Please note that with this you need to start the start date in the format of (Year, month, day). See code snippet below. It could be altered to get the min date of a particular table in the model. But to do this you would need to state the table name.

// Set Start Date

    StartDate = #date(2017, 1, 1),

I’ve included as many comments as possible to split up the code into sections such as year related columns, month related columns, day related columns etc.

DAX Method

Using DAX is another method. With this option, in the Power BI model create a new table. You can create a table easily by

• From the icons on the left select the Table View.
• Click Home in the menu bar.
• On the Home ribbon, click the New Table button.
• Paste the DAX code into the Formula Bar and click the tick.

Let’s have a look at the DAX code.

Set the variables.

The first step is to define the fiscal offset variable. Many organisations work on a fiscal year for financial reporting as well as using the general calendar dates. There are times where the fiscal month one is not always January. For this reason, an offset is applied to determine which month is month one. In the example in this article the code was taken from a project that was a UK education provider. The UK school academic year starts in September. As such the fiscal year will start in September. So, the offset that we use is 9.

Create a date column.

With this method the date range uses the DAX CalendarAuto() function that determines the date range from the model. For more information on this function there is a link provided below.

Define additional required columns.

Defining any additional columns would be on a case-by-case basis for each model. The common columns required include any year, month, and day related columns. Do you require season, quarter, or week columns for example.

Personally if the data is available at day level I will create this level of granularity in the model. You can easily roll up the data if reports are at month level. But if you have the granularity at the month level and the reporting requirements change so that you need to drill down to day level, it would be a challenge to change this to day level.

For additional columns then you will notice from the example code that the format function is used a lot. Once that you have a column with the date then from there the date can be formatted to apply additional columns. For example, to display the month name we use the format function. Tell it the date column to reference and the format string that we want to use. The syntax for this is very easy, if I want to display the full month name I would use FORMAT( [Date],”MMMM” ). This would display the text “January”. Alternatively with the same function but changing the format string I can display the month name shortened down. This would be FORMAT( [Date],”MMM” ). And this would display the text “Jan”.

DAX Function References

CALENDARAUTO – CALENDARAUTO function (DAX) – DAX | Microsoft Learn

FORMAT – FORMAT function (DAX) – DAX | Microsoft Learn

Conclusion

This code in this article will work just by pasting the code into the Power query window or with the DAX option by creating a new table and pasting into the code window. You may require some alterations to suit your requirements but from the base code you should have most required columns. Also, you should be able to see how this is put together and adapt accordingly.

Contact Us

If you require any support with Power BI data modeling or data engineering, please get in touch and one of the Datahub experts would be able to support any project that you have.

Working With Datahub Consulting

It wouldn’t cost you anything to start a conversation with our team of experts. If you are thinking of working with a consultancy on a technical project Datahub have successfully delivered projects in retail, aviation, healthcare, education, logistics, and gas & oil.

Contact us
Contact us | DataHub Consulting
Datahub Consulting Website
Data Consultancy Services | Datahub Consulting