Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
10 months ago
Solved

Inactive Relationship on Date Fields

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 bes...
Need Help
Show and Tell
  • mickey64's avatar
    mickey64
    10 months ago

    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's avatar
ArchStanton
Power Participant
10 months ago

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's avatar
mickey64
Super User
10 months ago

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's avatar
    ArchStanton
    Power Participant
    10 months ago

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