How to use a single date table for two date fields?

Question

I'm using Power BI Desktop to explore some data. I have a table called "Reports" which contains a list of reports that need to be filed. There are two date fields, "Due Date" and "Date Completed".

I have a single date table, Dates. It has been marked as my date table in Power Bi.

My goal is produce a matrix where each row is a month in the due date and each column is a month in the date completed. However, I can't figure out how to get Power BI to use the dates table for both of my date fields. I can join it in the relationships pane - but it looks like I can have only a single active relationship at a time. I've also tried the "Get Data" feature to add the date table multiple times, but it will only add once.

How can I use my date table for more than one date field at a given time?

Answer 1

You need to create two relationships to the dates table-- one is Active, one is Inactive. All measures will use the Active relationship, and to calculate data in the reports using the inactive relationship you create DAX Measures using the USERELATIONSHIP() function.

Here is a couple resources:

• https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
• https://carldesouza.com/power-bi-dax-userelationship-multiple-dates/