Issues with PowerPivot - two dates 'converted' from the same date-table

[Mr Pivot Rookie]

Hello!


First of all, I've been googling and browsing through 'DAX Formulas for PowerPivot' excellent guide without figuring this out, guess that's why my username contains 'Rookie' in it...


(I'm using Excel 2013)


Anyhow - I have set up PowerPivot and imported 3 other Excel-files:

• One table named [Allincidents] containing data extracted from our case management system
• One table named [Assignees] containing names and usernames of employees (not relevant in this question)
• One table named [DateTable] created in order to have Pivots with Quarters, Week numbers, Weekday names etc matched with the dates

I have created relationships from [Allincidents] to [DateTable] for 'Submit Date' and 'Resolved Date', and used RELATE to match 'Submit Date' with 'QuarterYear' and 'WeekOfYear' so that I was able to create pivots displaying cases submitted per quarter and week.


Now I want to do the same thing with 'Resolved Date' so that I can use both dates within the same pivot to create fancy reports - displaying how we're doing on the backlog of cases. But I'm running into problems, as I understand you can only have one active relationship to the same Table at once, making me have to choose between submitted and resolved dates.


I've tried to use USERELATIONSHIP etc, but I don't understand how I can put this into the column in the PowerPivot without getting errors.


Now let me show a few screenshots so it's easier to understand.


Note that 'Submit Date Time' is the originally imported data, and that 'Submit Date' has a =date function that removes the time as that's not necessary for my reports. Same goes for 'Resolved Date' in [Allincidents] and for 'DateKeyClean' in [DateTable].

Any tips are much appreciated. PS: Remember that I'm a rookie!

 

[Mr Pivot Rookie]

I was able to populate the "Resolved Week" column by using:

=CALCULATE (     CALCULATE ( 
        VALUES ( DateTable[WeekOfYear] ); 
        Allincidents
    ); 
    USERELATIONSHIP ( DateTable[DateKeyClean]; Allincidents[Resolved Date] ); 
    ALL ( DateTable ) 
)

Sweet :D


However, when I try to do the same thing for the "Resolved Quarter Year" column I get the following error:

A circular dependency was detected: 'Allincidents'[Resolved Week],'Allincidents'[Resolved Week],'Allincidents'[Resolved Quarter Year],'Allincidents'[Resolved Quarter Year],'Allincidents'[Resolved Week].

It seems that I cannot use the =CALCULATE twice, as if I remove the calculation for "Resolved Week" and add it for "Resolved Quarter Year" instead it works just fine.


What else do I have to do to get both working at the same time?