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.

     

     

     

     

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.