Using The M Function To Create An Extended Power BI Date Table

This tutorial will cover about the most important Power BI dimension table in any model, the Date Table. You may watch the full video of this tutorial at the bottom of this blog.

The best practice when designing a model is to check for date fields. If they are present, always include a date dimension table.

A Date Table includes all the attributes that you may want to slice, dice, and filter your data with to get valuable insights. With it, a date field anywhere inside your model won’t be a problem.

Table of Contents

Get The Power Query M Code In The Forums

In the Enterprise DNA Forum, you can type Date Table in the search bar, or switch to Categories and look for M Code Showcase. But the quickest way to find it is to look for “@melissa”.

Next, choose any subject, then click the badge, and then click the Featured Topic Extended Date Table.

You will then find the M code.

Copy this whole measure and transfer it to the Power Query.

Application In Power Query

For this step, make sure the formula bar is visible. If it isn’t, go to View tabs and enable it.

Right click the Parameter and Function folder, choose New Query, and then Blank Query.

Then click the Advanced Editor and paste the Power Query M code. After that, change the query name to fxCalendar.

You need to set up the parameters before you can invoke this function. Set it up with the following details:

Invoke the function and then rename it to Dates.

It will now show you this date table with 32 columns. If you’re familiar with the M Code, you’ll probably recognize some columns here. The other columns are additional attributes or logic from the code that you used.

In the Date Table shown, you can see Fiscal Year, Quarter, and Period columns, as well as the filter columns, IsAfterToday, IsWorkingDay, and IsHoliday.

Since the 4th parameter wasn’t set up, IsHoliday bears an Unknown value.

Moreover, included in this Date table are the Offset columns which simplify the DAX code you need to do some analysis.

If you want to add attributes, use the code and amend it to what you want. Also, if you want to delete unnecessary columns, go to Choose Columns and then deselect what you don’t need.

Make A Dynamic End Date

When developing a report, the start date is easy to set unlike the end date. The end date is tricky because it depends on the data inside your model and how long your report remains active.

To make a dynamic end date, we need to dynamically update the year.

First, create a new blank query.

The M equivalent of the DAX or Excel function is “DateTime.FixedLocalNow”. This is what the function does:

Next, in the formula bar, use the function to extract the year value.

As you reach the end of the year, your data may spill over the next year. So, it’s best to add an additional year like this:

After that, rename the query “MaxYear”.

Go back to your Date Table, and sub in the MaxYear variable in the function to have the year 2021 in your calendar.

Invoke And Get The Holiday List Column

If you check your holiday table, you can see that it runs from 2018 to 2020 even if the max year is 2021.

To alter this, go to Source Step, and then sub in the MaxYear variable in the measure.

Your holiday table will then have the year 2021.

Next, go back and set up the parameters again. But this time, include the Holiday parameter by clicking Choose Column.

Select Holidays and the column that contains the dates then click OK.

After setting everything and clicking Invoke, you will see the “Holidays[Date]” variable in the formula bar. Copy that variable and delete the Invoked Function.

Go back to the Date Table, then sub the variable in the Source Steps’ function.

Once it finishes loading and invoking the function, you will see that the IsHoliday column that once had unknown values now has True or False values in each row.