How To Create Date Table In Power BI

In this post, various methods of generating a date table in Power BI are demonstrated
Published

March 12, 2023

Introduction

An example of a date calendar in Power BI.

In Power BI, date tables are typically essential to the creation of a successful data model.

They provide a way to organize and analyze data over time, allowing for comparisons and trend analysis. A date table in Power BI can be created in various ways, each with its advantages and disadvantages. In this post, we will explore some common methods of generating a date table in Power BI and provide step-by-step instructions for each approach. Whether you’re new to Power BI or a seasoned pro, you’ll find this guide helpful for creating a date table that meets your data modeling needs.

3 types of date tables will be demonstrated, which will provide you the knowledge to overcome any of your date table needs:

  • Daily

  • Monthly

  • Hourly

For the example data model in this post, I will be using a table of my body weight by day(Fct_Body weight), as well as the weight lifted for deadlifts(Fct_Gymbook).

The Problem

An example of a date calendar in Power BI.

Fig 1.

Lets say you have 2 fact tables in your data model like those on the right (Fig. 1).

Your desire is that if you filter on a particular date, you will see results in both tables corresponding to your date filters.

If you are new to Power BI, your temptation may be to link the 2 tables directly via the date in the data model, HOWEVER, if you do this:

  1. Filtering on the date of one table will not provide data from the other table when a particular date does not exist
  2. If you add in a 3rd table, it will also need to be linked, and the above issue will be further exacerbated.
  3. If you try to add link the tables to additional dimensions (IE, maybe I want to link to a table that categorizes the muscles used in the exercise)
  4. AND MORE!!!

It may initially appear to give you what you desire, but if it doesn’t fail you now, it will once your model becomes more complex.

The Solution

A data model which makes use of a date table as a dimension as well as 2 fact tables which are connected by date and month

Fig 2.

To overcome the issues mentioned above, you will want to link your 2 tables (In this case Fct_Body Weight and Fct_Gymbook) to a date table (Dim_Monthly Calendar in Fig 2.)

Once the data is modeled this way, your calendar table can then be used to filter all tables connected to it.

In Fig 2, this has bee accomplished using a calendar aggregated by month, but in case this is not suitable for your data, examples below will provide Monthly, Daily, and Hourly aggregation.

Daily Calendar

Dim_Daily Calendar = 

VAR Max_Valid_Date = TODAY()

VAR Min_Valid_Date = DATE(2022,4,1)

RETURN

ADDCOLUMNS(
    CALENDAR(Min_Valid_Date, Max_Valid_Date),
    "Month", EOMONTH([Date], -1)+1,
    "Month_numeric", MONTH([Date]),
    "Month Year", EOMONTH([Date], -1)+1,
    "Month_text", FORMAT([Date], "MMM"),
    "Last 12 Months", [Date] >= EOMONTH(TODAY(), -13),
    "Is Current Month", [Date] >= EOMONTH(TODAY()-1, -1)+1,
    "Is Previous Month", [Date] >= EOMONTH(TODAY()-1, -2)+1&& [Date] <= EOMONTH(TODAY()-1, -1)
)

Explanation:

  • Max_Valid_Date will determine the end date that you would like your calendar to end.

  • Min_Valid_Date will determine the start date that you would like the calendar to start at

  • CALENDAR() is a function. Based on the start and end date provided to it, it returns a table consisting of the dates from the start to the end.

  • ADDCOLUMNS() is a function. This first argument is a table (in this case we are providing it with the calendar generated by CALENDAR(). Each subsequent set of arguments are provided in pairs where the first value is the name of the new column you’d like to add, and the second value is the method in which it is calculated (IE “Month” is the name of the new column which is determined by obtaining the first day of the month from EOMONTH())

Monthly Calendar

Dim_Monthly Calendar = 
VAR Max_Valid_Date = EOMONTH(TODAY(),-2)+1
VAR Min_Valid_Date = DATE(2021,1,1)
VAR MonthList = SELECTCOLUMNS(
    CALENDAR(Min_Valid_Date, Max_Valid_Date),
    "Month",EOMONTH([Date], -1)+1)

RETURN

ADDCOLUMNS(DISTINCT(MonthList),
    "Month Text", FORMAT([Month], "MMM"),
    "Month Numeric", MONTH([Month])
)

This method will build on the daily calendar method, but here, 2 steps are taken to modify the daily calendar into a monthly one:

  1. SELECTCOLUMNS() is used to select the Month column. At this point in the code, month is repeated once for each date.
  2. DISTINCT() is then used to reduce Months so that each month only appears once.
  3. ADDCOLUMNS() is then used to add additional columns (In this case, month text and month numeric) desired to the final table.

Hourly Calendar

TimeTable = 
  VAR dateTime = 
  GENERATESERIES(
     DATE(2023,01,01),
     DATE(2024,01,31),
     TIME(1,0,0)
  )

  VAR final_table = SELECTCOLUMNS(dateTime,
  "DateTime", [Value],
  "Date", DATE(YEAR([Value]), MONTH([Value]), DAY([Value])),
  "Hour", TIME(HOUR([Value]), 0, 0),
  "Hour_numeric", HOUR([Value])
  )

  RETURN
  final_table

This method makes use of the GENERATESERIES() function by supplying a start and end date then making hourly increments by using the TIME() function.