Forum Discussion

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 i...

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

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
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?
- Anonymous
  Not applicable
  4 years ago
  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