Power BI: How to show different date calculations on the same date axis.

Problem:

Let's say you have a ticketing system and you want to show a count of tickets based on their created date and also on their completed date. You also want to show both of these calculations (measures) on the same date axis. How do you accomplish this?

Other examples would include order date, shipping date, etc.

Calculations with different dates causes issues using a single axis

Solution:

The solution is to use a combination of a date table, measures, and a little bit of DAX magic. The end result will have a single axis with multiple calculations.

How to implement:

1) Create a date table

A. The first thing you want to do is create a date table. There are many ways to do this, but the important thing is to capture the full range of dates you have in your data set. For this example, I used DAX.

Create a new table with DAX

Date table with CALENDAR function

---

B. DAX Formula to create a date table:

DateTable = CALENDAR(
MIN(Tickets[Created Date]), 
MAX(Tickets[Completed Date])
)

I'm using the minimum created date and maximum completed date. This is because created dates should always fall before the completed date.

---

C. Mark this table as a Date Table.

D. Go ahead and create a date hierarchy for the dates. This will allow you to group by months and have that nice drill down functionality.

2) Establish the relationships

A. Next, you'll need to create the relationship from your date columns to the date table.

B. Change this relationship to inactive.

Now you'll see a dotted connection line.

C. Do the same for the other date.

Turning both relationships inactive will preserve the expected behavior of Power BI's filtering (i.e clicking on interactive charts & visuals). If both relationships were active, on the other hand, we might see some unexpected filtering behavior, causing confusion down the road.

3) Create the measure with USERELATIONSHIP()

A. Now you just need to create measures for each date column. Here we will use a combination of the CALCULATE function with USERELATIONSHIP as the filter parameter. Because the relationships we established are inactive (see above), the userelationship() function will allow us to use them as if they were active.

Count of Created Tickets = 
    CALCULATE(
        COUNTROWS(Tickets),
        USERELATIONSHIP(DateTable[Date], Tickets[Created Date])
        )

Count of Completed Tickets = 
    CALCULATE(
        COUNTROWS(Tickets),
        USERELATIONSHIP(DateTable[Date], Tickets[Completed Date])
        )

4) Build the visual

A. Now just drag these measures onto the visual. Be sure to use the date table date column as the axis!

B. And now your visual should look something like this.

Cheers!