What Are the Opportunities for Building Day Tables in Do-It-Yourself Areas?

An example of this is calculating the number of weeks of Fiscal Years if they do not correspond to calendar years.

Doing this with a math formula is a nightmare, or my math skills aren't sophisticated enough.

A Question of Power and Data Flow

Now we come to the final variant: Using Power Query or Data Flow.

First of all, I don't differentiate between Power Query and Data Flow in v1 or v2, as they all work on the same principles and use the same language.

I start creating the date table in PowerQuery by creating three parameters:

1. First year: The first year in the date table
2. YearsToLoad: How many years to load in the date table
3. First Month of the Fiscal Year: Which is the first month of the Fiscal Year.
   If the Fiscal Year corresponds to a Calendar year, this will be 1; otherwise, it will be the number of the first month of the fiscal year.

All additional code will depend on these parameters.

The beginning always has the same command: List.Dates()

The parameters of this work are:

1. First date
2. The number of days to create the list
3. An interval, in this context days

This leads to a line like this, while using the parameters mentioned above:

List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0))

And here is the first hurdle:

Usually, we need a date table that spans many years. But every fourth year is a leap year.

So, how can we do this, since Microsoft requires a date table that spans all years?

The solution is to find the last date of the previous year (31. December) and filter the rows to keep only those before or equal to this date.

And this is the reason why I am multiplying 366 days with the YearsToLoad parameter.

Here is the full M-Code for this condition:

let
    Source = List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Last Valid Date" = Table.AddColumn(#"Changed Type", "Last Valid Date", each #date(Date.Year(List.Max(#"Changed Type"[Date])) - 1, 12, 31), type date),
    #"Keep only valid dates" = Table.SelectRows(#"Added Last Valid Date", each [Date] <= [Last Valid Date])
in
    #"Keep only valid dates"

Next, I can start adding all the necessary columns to create a complete date table.

First, I add Date_ID, which contains the date number:

Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date])

This column must be set to the integer data type. So, the whole line of M-Code is:

Table.AddColumn(#"Keep only valid dates", "Date_ID", each ( Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date]), Int64.Type)

Note the expression Int64.Type before the closing parenthesis. This sets the data type in the same command, eliminating the need for an extra step.

Next, I can use the possibilities available in the Power Query Editor to add additional columns that I often add to my date tables:

As you can see, we can add a large number of columns without writing code.

But at some point, we have to write our code to add additional columns—for example, columns to store the corresponding year and time.

Here are some of these columns:

• Year/Month Name
• Year/Quarter
• Year/Week

Then, in the start and end date columns for any period, such as week or month.

I'm using these columns with custom time code for intelligence in DAX. I have written other pieces here on this topic, such as weekly statistics.

And at some point, the usual M-Code is not enough to get the necessary information.

For example, if I need to get the year column aligned with the week (YearForWeek).

In these cases, I started writing custom M functions that allow me to access the date range for each row, which is not possible with M.

For this, I added this function:

(DateInput as date) as number =>
let
    ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),
    Year = Date.Year(ClosestThursday)
in
    Year

If you are not familiar with custom M functions, I strongly recommend you check out this excellent feature.

I will add some links in the References section below.

After all the update of the date table, I got these custom functions:

• GetISOYear
  Find the year that corresponds to the week
• GetISOWeek
  Calculate the correct week number based on the ISO Level
• CalculateMonthDiff
  The difference in months between two days
• CalculateQuarterDiff
  The difference in quarters between two days
• GetFiscalWeekNumber
  Calculate the week number starting from the week of the day the Fiscal Year begins.
• GetCurrentFiscalYear
  This gets the current Fiscal Year based on the current date.
• GetCurrentFiscalStartYear
  This counts the year in which the current Fiscal Year begins.

It took me some time (2-3 working days), but I was able to merge all the columns in the date table, which I find useful in many situations.

But the basic concepts in the M language added more work and complexity, which is not needed, for example, in SQL.

But instead of copying all the M-Code here, I'll give you access to the Power BI file that contains the entire solution with the date table.

What's next?

Well, now you can take all the M-Code, copy it into a data flow, and share it throughout your organization.

To allow access to your data flow, granting viewer permissions to consumers in Workspace is sufficient.

This way, you have one centralized version of the date table that everyone can use.

This is the main point that makes this method very useful.

It's the same when you have a central database, where you build a date table. But since not everyone has this, using Data Flow is a middle ground.

In my work with Data Flows, I have found that troubleshooting failed imports can be difficult. I have found that error messages can be small and may miss important information.

Which one would you use?

What do I recommend using?

First, if you have a central data store, whether it's on-premises or cloud-based or if it's a relational database or other data store, use this to build your date table.

As I said, there is no question about this.

In a self-service BI situation, or if the company is not that big, the decision is less straightforward.

First, it depends on the skills available.

After creating a date table in Power Query, I found that it is much easier to create a date table in DAX than in Power Query.

DAX capabilities make it easier to build a date table than M-Code for a power query.

I can define a table in a single DAX statement and add complex logic to additional calculated columns.

But each DAX date table is local to each Power BI semantic model. Therefore, you end up with multiple date tables that can diverge from each other.

But once you have multiple teams building Power BI solutions, it can be beneficial to create one central date table in one Workspace and share it with all teams.

If someone needs a new feature in the date table, it will be added to the central table, and everyone can benefit from it.

Of course, this is valid for any variant of intermediate date tables.

In such cases, the data model developer can always decide which columns to import, avoiding the import of unnecessary columns in the data model.

The conclusion

Now you know the different ways to create a date table.

You decide between the available possibilities.

But it will be difficult to switch from local DAX table to any central tables.

You should think in advance which way you will go to avoid the extra work of switching between them.

Take your time and talk to all the team members or potential model creators to choose the right approach.

It makes no sense to decide to create a centralized date table when no one is using it.

So, make sure everyone is on board using the average date table.

References

Here, Microsoft's documentation about custom functions in M:

Page on Microsoft Learn about custom functions:

A good explanation about Wicked Smart Data:

If you choose a video to read, this one explains custom functions from the bottom:

This video asks the question of when custom functions are useful:

This shows you how to solve the challenge, including a practical way of how to develop a custom function easily: