PowerBI, what if you have multiple date fields

In Microsoft Power BI (or Power Pivot in Excel) if you want to work with dates then you will usually need a Date table. Date sliders and the date based DAX functions rely on there being a date table linked to fields you want to work with. But what if your data has multiple date fields in the same table that you might want to use? For example order data might have date order recieved, date order dispatched, date payment due, date payment received and a number of other dates associated with it (e.g. date account put into debt managenent). Similarly project task information may have date created, date due, date started and date completed.

In the data model you can only have one active relationship between two tables, including where there is an implicit relationship via an intermediate table, and you can only have one date table, so what do you do if you have multiple date fields you wanrt to use? The answer is references.

A reference is a query that takes as it’s input the result of another query. You can then use the data as is or apply further changes, this is useful where you might want to carry out different transformations on the same initial data and there are initial steps common to all of the transformations. As a reference takes the output of the other query, if you make changes to the original query they are carried through into the reference. If you want to copy a query and not have later changes to the original carried through into the copy then use the Duplicate option.

One slight downside to this method is that you have to create the date table outside of the Power BI file and then read it in.

When you create them the reference queries will be named the same as the original query with a number on the end, e.g. a reference on Dates may be called Dates (1). Generally it would be best to rename them to match the name of the field they will be linked to so the reference query that will link to Created Date might be renamed to Created Dates. Good practice would be to create a reference for every date field that might be needed and not reuse the original date query. This reduces the risk of accidentally making changes to the original query that might have a knock on effects on the reference queries.

Once you’re done, close and save your Power Query window then in Power BI confirm that your originakl query of marked as the Date table and thne go to the relationships view and create your relationships:

As we can see here each refererence query is named for the field it will connect to and the relationship has been created. The original query (Date) is shown but not linked to the data table at all.