Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower 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.

     

     

     

     

mickey64's avatar
mickey64
Icon for Super User rankSuper User
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
    Icon for Power Participant rankPower 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
      Icon for Super User rankSuper 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
        Icon for Power Participant rankPower Participant
        10 months ago

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