Trouble using CALCULATE on a measure that has USERELATIONSHIP

[jdbuchanan71]

Good morning,

I am trying to calculate a measure applying a filter where the difference between 2 dates on the data is <= 60 days.  The measure I am trying to calculate uses the USERELATIONSHIP function and I think that might be what is causing the problem.  The measure by itself works fine.

Paid Amount = SUM ( data[Paid] )

Paid Amount by Paid Date = 
CALCULATE ( [Paid Amount], USERELATIONSHIP ( data[Paid Date], dates[Date] ) )

But when I try to apply a filter to [Paid Amount by Paid Date] is where it goes wrong.

Paid within 60 measure = 
CALCULATE (
    [Paid Amount by Paid Date],
    FILTER (
        data,
        data[Paid Date] - data[Service Date] >= 0 && data[Paid Date] - data[Service Date] <= 60
    )
)

I have been able to work around it by adding the number of days to the data table but that isn't really a scalable solution and I thought it should be possible with measures, I just cant get it to work.

The link below contains a sample model showing the three matrices.  

1. The whole view, no filter on days

2. The view with the filter applied to the calculated column.  Works but not scalable.

3. The view with the broken measure.

I am trying to get 3 to look like 2 using the measure.

https://www.dropbox.com/s/ec5922qgkkvsk37/Paid%20within%2060%20model.pbix?dl=0

Any help is appreciated.

[HotChilli · Accepted solution]

I was thinking that filtering the table first would be more efficient, so i've come up with two measures

xPaid Amount by Paid Date = 
    CALCULATE ( SUM ( data[Paid]), 
                FILTER(data, 
                        DATEDIFF( data[Service Date], data[Paid Date], DAY) <= 60 &&
                        DATEDIFF(data[Service Date], data[Paid Date], DAY)  >= 0)
)

and then tell powerbi which date to use

Measure = CALCULATE([xPaid Amount by Paid Date], USERELATIONSHIP(data[Paid Date], dates[Date]) )

Please give it a test

[jdbuchanan71]

Thank you @HotChilli that worked perfectly and is very fast.  

I also tried the same solution in a single measure using variables but was not able to get the measure to respect the USERELATIONSHIP function.  It is like variables ignore it.

Paid in 60 using variables = 
    VAR PaidIn60 = CALCULATE( [Paid Amount] , FILTER( vclaim, vCLAIM[Paid Date] - vCLAIM[Service Date] >= 0 &&  vCLAIM[Paid Date] - vCLAIM[Service Date] <= 60 ) )

RETURN CALCULATE( PaidIn60 , USERELATIONSHIP( vCLAIM[Paid Date], DATES[Date] ) )

Just thought that behavior was odd so I wanted to share.

Thanks again for your help!