USERELATIONSHIP with DATEDIFF

[Anonymous]

Hello Community  -  The VAR statment is what I am trying to understand here, in my calculated column.    I am not getting any errors in the formula itself (no red lines, etc), but when I go to save it, I get an error saying the USERELATIONSHIP function can only be used in the CALCULATE function.   I do have it wrapped in a Calculate function, so not clear how to correct?  

Leadtime Rules =

VAR _Days = CALCULATE(DATEDIFF(USERELATIONSHIP('Dim_Date Table'[Date],'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]),

TODAY (),

DAY

))

RETURN

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -15

&& _Days > -1400,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -8

&& _Days >= -14,

"Danger Zone",

IF (

[AlexisOlson]

The USERELATIONSHIP does not belong inside DATEDIFF. Try this instead:

CALCULATE (
    DATEDIFF ( 'Dim_Date Table'[Date], TODAY (), DAY ),
    USERELATIONSHIP ( 'Dim_Date Table'[Date], 'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c] )
)

[Anonymous]

@AlexisOlson    I tried your measure but unfortunatley the intellisense won't pick up the dim_date table (date) column.   

[AlexisOlson]

OK. Can you explain why you're trying to use the USERELATIONSHIP function in what appears to be a calculated column?

Do either of these work instead of the CALCULATE expression I gave previously?

DATEDIFF ( 'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c], TODAY (), DAY )

DATEDIFF ( RELATED ( 'Dim_Date Table'[Date] ), TODAY (), DAY )

 If not, why not?

[Anonymous]

Hi @AlexisOlson    -   Here is my original formula  (at least the VAR part).   It works fine...except:  

I used this calculated column to create a "group" using the "create group" feature.   And then assigned numeric values to each of the results below....1 = Past Due, etc.    I then used that group (the 1, 2, 3, etc) in the conditional formatting.    Everything worked fine until I changed the relationship in my data model.   I connected my date table to the Opportunity header table, versus the Opportunity Line table.   And now I am getting a circular reference error.   So....I thought changing the formula might work since I changed the data model.   Below is the error message I get after I try to save the group.   I've also include a picture of a portion of the data model.  

VAR _Days = DATEDIFF(

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c],

TODAY (),

DAY

)

RETURN