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.

     

     

     

     

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
10 months ago

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]))