3 Ways to Improve your reporting with an expanded date table

In most cases, date is the most crucial reporting dimension. Most of the time, you want to see the development of a figure over time, for example, the sales amount over the years, months and days.

It could be possible to use a date field in your data to create the report. But this approach has some drawbacks.

Let’s look at this topic and how to improve your reporting capabilities with an expanded Date Table.

Why do we need a date table in the first place

You can skip this section if you are familiar with Reporting and the use of date (or time) tables.

There are four main reasons why you need a date table:

You can define a time-span for your data and your reporting. You need to be aware, that most reporting tools need a date-table that start on January, 1st and end on December 31.
You will have the ability to create navigation hierarchies over year, semester, quarters, months and days or weeks: Some reporting tools support the creation of automatic date hierarchies, but some not. When you don’t have this ability, you need a dedicated date table.
You can add custom columns to your date table as you need Read below for more on this topic.
To be able to compare data with different regularities: This situation needs some more in-depth explanation.

Let’s assume that you have data with measurements, which is not measured every day; For this example, let’s use the daily energy consumption.

Then, you have data about the daily energy generation, for example, from your solar panels. This data is measured daily.

A chart with both data side by side will look like this:

You can see that there are gaps in the measurement.

With a small trick and the use of a date table, I can fill the gaps:

I copied the values from the date of the measurement to the day before the next measure.

On the month view, this would look like the following picture:

The top chart shows the data with the gaps. The bottom chart shows the data with the filled gaps.

In this case, the bottom chart is correct, as it sums up the daily energy consumption, instead of summing on the measured consumption only.

This operation is relatively effortless with the help of a date table. In my case, I stored the data in a database and prepared the data with a SQL query.

Now we know four reasons why we need a date table. But there are many more reasons to have a date table, which I cannot explain here. It will take too much time to explain all reasons.

Now let’s look at how you can extend your date table with more information.

Add custom columns

You should add additional columns to the date table to make sure that you can slice your data in the way you need it.

For example:

Short month name: Jan, Feb, Mar, etc.
Short month name with year: Jan 2021, Feb 2021, Mar 2021, etc.
Other periods with year, for example, quarter or semester with year.
Weekday (long or short): Monday/Mo, Tuesday/Tu, etc. Of course only when you need to create a report on Weekday basis.
If you need to report on a weekly basis, you need to calculate the week number. Take care that there are two different methods to calculate the Week number (for example the ISO 8601 method)
Multilanguage columns in any needed language

Here some examples:

Add Flag-columns

Sometimes you want to see only data from the actual period. A Period can be:

The actual week
The actual month
The actual quarter or semester
The actual year
Any specific period based on the actual date For example, the last six months, the actual month and the next five months – equals 12 months.

Such flag-columns contain only 1 or 0; one for the rows in the actual period and zero for all other rows in the date table.

Here some examples:

One more flag-column is "IsWorkday". You can use the flag-column to mark all working days and exclude weekend-days.

You can simplify the filtering of your data with such flag-columns.

Think about this situation: You have project information with data from the past and planning data for the future. Now you need to see the actual data and the planned data for the actual month, some months from the past and into the future.

You can use complex date calculations to get this range of data. Or you configure this time window into your date table in a flag-column. Then you only need to filter your data based on this single flag-column.

This approach is much easier to implement with the appropriate flag-column. Also, the performance is much higher, as it is unnecessary to perform complex date calculations in the report.

Add Index-columns

Index columns are numerical columns, which counts periods from today.

Like the flag-columns, you can have multiple index-columns in your date table:

DayIndex – Number of days since today
WeekIndex – Number of Weeks from the actual week
MonthIndex – Number of Months from the actual month
YearIndex – Number of Years from the actual year
WindowIndex – This is based on the "IsInActualWindow" flag-column, and shows the actual date-window position.

The preparation of these index-columns must be clearly defined.

Consider the WeekIndex column:

The actual date is February 14. 2021
This date is Sunday.
Depending on the region, Sunday is the last or the first day of the week.
You need to know your audience to define which case needs to be applied.

Another example:

The Actual date is February 10. 2021
This date is Wednesday.
Depending on your needs, you need to calculate the index based on the actual date +/- 7 days or the actual calendar week.

Based on the calendar week, you can use a formula like this (Based on Monday as the first day of the week and the ISO 8601 method):

DATEDIFF(Now() - WEEKDAY ( Now(), 2 ) + 1, [Date] - WEEKDAY ( [Date], 2 ) + 1, WEEK)

I used the DAX-Language this formula. Microsoft Power BI uses DAX to implement calculated columns.

You need to take care of the calculation of the MonthIndex column as well. You need to define if you calculate the index based on the calendar month, for example, based on the last, or first, day of the month or based on another base.

In DAX I used this formula for the MonthIndex column:

DATEDIFF(EOMONTH(Now(), 0), EOMONTH([Date], 0), MONTH)

The function EOMONTH() returns the last day of the actual month. This way, I always use the calendar month for this column.

Now you can use these index-columns to further simplify your reporting, instead of relying on a date-based calculation, which you need to implement multiple times.

Conclusion

When you start building a date-table, you need to be aware of your reporting needs.

You can start adding additional columns to the date table, to improve your report’s capabilities and simplify the time calculations’ logic.

But, you need to take care to use the correct logic, when you implement the flag- and index-columns.

After you have completed your logic, you need to make sure that you update your date-table daily. The flag- and index-columns are only correct with a daily update.

The last benefit to use additional columns in a date table is that the logic is centralized.

The centralization of the logic can be a benefit or a drawback. When the logic is correct, you can rely on it, and everyone is happy. If it’s wrong, then it will be wrong for everyone.