Inactive Relationship on Date Fields

[ArchStanton]

I have had to create 2 extra calendars because my visuals fall apart whenever I try to establish an Inactive Relationship between secondary date fields and my main Date calendar - this is against best practice according to all literature on the subject but it simply doesn't work for me.

My main (prime relationship in the data model) is Date2[Date] Calendar Table to the Cases[Created On] in the Cases Table

I also have a Resolution Date in the Cases Table (which is not continuous list of dates).

The first thing that goes is the month x-axis on my column chart because these months are not based on the Resolution Date, they are the main default Created Date months.

Fig.1.

It should look like this:

Fig.2.

I have a filter on the Fig.1. visual to show all cases resolved after 31st Mar 2025 but it still shows Jan-Feb-Mar values in the x-axis. It seems like I have no option but to add more filters on the actual month names and that is unamanageable each month.

I want to use the abbrevaited 3 letter Month names and not the full name of each month because space is a premium in my visuals.

All of these problems made me decide to build a separate calender table that directly links the Cases[Resolution Date] to Date2[Date]. 
Unfortunately I cannot share my pbix file because of data sensitivity ete.

Has anyone else had these issues and if so, how were they tackled?

[ArchStanton]

I've added a FY filter to the DAX but can anyone suggest a way of ameding the code without me having to do this?
This will mean I will have to create a new measure in Apr 26 and it would be much better if the YTD would simply work.

YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Cases'[Resolution Date],"31/03"),
        Date2[FY] = "FY2025/2026",
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

[mickey64 · Accepted solution]

For your reference.

Step 0: I use these data below.

Step 1: I make a 'Date2' calendar table below.

    Month_Format = FORMAT([Date],"mmm")

    Month No = MONTH([Date])

Step 2: I add two relationships below.

<Active>

<Inactive>

Step 3: I make two measures and make a 'Clustered column chart' below.

    Value on Created Date = SUM('Cases'[Value])

    Value on Resolution Date = CALCULATE(SUM('Cases'[Value]),USERELATIONSHIP('Date2'[Date],'Cases'[Resolution Date]))

Step 4: I make a 'Slicer' below.

[ArchStanton]

Hi, I've managed to get the following to work using USERELATIONSHIP:

YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Cases'[Resolution Date],"31/03"),
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

I still need to manually add a FY Year filter for the visual to work if I don't this is what I see:

This should only show Apr to Oct

Is there anything I can do to the code that will add the 2025/26 FY filter to it automatically?

[mickey64]

For your reference.

How about this formula below.

YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Date2'[Date],"31/03"),
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

[ArchStanton]

thanks, it gives me the same result as my measure, I need the additional filter as well.

[Ashish_Mathur]

Hi,

Share some data to work with and show the result in a simple table format.  From there, we can build any visual we want.  Share data in a format that can be pasted in an MS Excel file.

[vojtechsima]

Hey, @ArchStanton ,
I am not sure I get your struggle, but here's how you can easily pull single calendar with two relationships:

Setup:
Date is active, ResolvedDate is inactive

Measures like this:

activeMainSum = SUM(fact5[Revenue])

InactiveSum = 
 CALCULATE(
    SUM(fact5[Revenue]),
    USERELATIONSHIP(dim_cal[Date], fact5[ResolvedDate])
 )

active and inactive together = [activeMainSum] + [InactiveSum]

Result:

And I always use dim_call to display dates, I created a column with the "MMM" format. Any filter you apply will act based on the date assigned to that calculation.

[ArchStanton]

Hi, I've managed to get the following to work using USERELATIONSHIP:

YTD CasesClosed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Resolution Date]),
        'Cases'[Resolution Date],"31/03"),
            USERELATIONSHIP(Date2[Date],'Cases'[Resolution Date]))

I still need to manually add a FY Year filter for the visual to work if I don't this is what I see:

This should only show Apr to Oct

Is there anything I can do to the code that will add the 2025/26 FY filter to it automatically?