Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

USERELATIONSHIP with DATEDIFF

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 (
Need Help

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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?