Power BI: Using multiple date fields

Like several similar solutions, Power BI works best if data are structured in a star schema, which is a structure that consists of fact and dimension tables. Typically, one of these dimensions is the date dimension. This dimension can filter your data based on calendar-type fields. If there are multiple Date fields in the fact table (such as OrderDate, DueDate, ShipDate…), there are some considerations to take into account.

Date Dimension

When creating a data model that starts with a facts table, Power BI will usually create a date dimension in the background for each of the date fields. This can come in handy if you’re working with a very simple model; however, the hidden data table or dimension is limited to day, month, quarter and year. If you’d like more options when creating custom date fields, it’s better to use a specific date dimension.

Using Multiple date fields

Once you’ve defined the date dimension’s structure, you need to create a relationship with the facts table. This is the tricky part: Power BI can only have a single active relationship between two tables. If you want to have multiple date fields in the fact table, you’ll need a workaround.

One possible solution would be to duplicate the date dimension as many times as there are date fields; however, this also implies using multiple slicers to select e.g. a year range (one for each field/dimension). Consequently, it’s not the most convenient solution.

Instead, we’ll create a second, third… hence inactive relationships between the date dimension and the facts table. These relationships will become inactive as you create more. Which relationship becomes active depends on the usage: typically, this is the most-used relationship or the initial “datestamp” (e.g. OrderDate) in the process.

Now that we’ve got active and inactive relationships, we’ll need to set up specific measures to reflect the results based on the “inactive” date perspective. We’ll use a formula that looks like this:

Measure =
CALCULATE (
[M_ActiveDate];
USERELATIONSHIP ( DIMDATE[Date]; FACTS[Date_inactiveRelation] )
)

This temporarily activates the required relationship to execute the respective measure.