Using CALCULATE, COUNT, FILTER and USERRELATIONSHIP

Hi,

I'm struggling to make this work using CALCULATE, COUNT, FILTER and USERRELATIONSHIP all in the same formula with Power Pivot so hopefully someone could help me please.

I have a table called Register that shows all new business submitted and also when it is in force, or ‘On Risk’, there are columns called ‘Submitted Date’, and another called ‘Date On Risk / NTU’ to show the respective dates. I want to be able to count the number of new business applications that are On Risk and not count ones that are NTU so that is why I’m using a filter.

I currently link the table to two separate date tables so that I can identify the Submitted Date and Date on Risk, but I want to just use one Date table called ‘Dates’ and incorporate the function USERRELATIONSHIP.

I have setup two expressions called ‘L12M’ and ‘ReportDate’ which work so that is not the issue.

My current formula that works when I use two date tables is

CALCULATE(COUNT(Register[On Risk / NTU), Register[On Risk / NTU] = "On Risk",
FILTER(Register, Register[Date on Risk / NTU] > [L12M] && Register[Date on Risk / NTU] <= [ReportDate]))

I have created the active relationship between Dates[Date] – Register[Submitted Date] and the inactive relationship between Dates[Date] – Register[Date on Risk / NTU]

My formula I’m trying to use is as follows

CALCULATE(COUNT(Register[On Risk / NTU), Register[On Risk / NTU] = "On Risk",
FILTER(Register, Register[Date on Risk / NTU] > [L12M] && Register[Date on Risk / NTU] <= [ReportDate]),
USERRELATIONSHIP(Register[Date on Risk / NTU], Dates[Date]))

The Register will look like this

I’m not getting any error messages, but it is not returning any value, using the above table it should return the count of 1. So how can I use the USERRELATIONSHIP function so that I can just use one Date table please.

Any help would be appreciated.