Forum Discussion

Anonymous

Not applicable

4 years ago

datediff with userelationship

I need to change this calculated column to use an inactive relationship between my date table, and the date mentioned below. Previously, I had this as an "active" relationship but have since made it inactive. Having a hard time getting userelationship to work but I know that a Calculate statement may need to be included somewhere. Any suggestions?

Leadtime Rules =

VAR _Days = DATEDIFF(

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c],

TODAY (),

DAY

)

RETURN

IF (

6 Replies

amitchandak
Super User
4 years ago
Anonymous ,Can you share sample data and sample output in table format?
- Anonymous
  Not applicable
  4 years ago
  Here is the full code, but I cannot share the file. Actually the formula is working.
  
  However, when I try to create a "group" from this column, I am getting a circular reference error and I need this group for something else. The circular error reference is referencing the SF_Opportunity Line table, which is not related directly to the Date Table (the Date Table is directly related to the SF_Opportunity table, and the Opportunity Line table is related to the Opportunity table).
  
  Leadtime Rules =
  VAR _Days = DATEDIFF(
  '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 (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= 0
  && _Days >= -7,
  "No Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days > 0
  && _Days >= -7,
  "Past Due",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -50
  && _Days > -1400,
  "Upcoming Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -36
  && _Days >= -49,
  "Danger Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= 0
  && _Days >= -35,
  "No Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days > 0,
  "Past Due",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= 0
  && _Days > -1000,
  "Upcoming Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -50
  && _Days >= -63,
  "Danger Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days >= -49,
  "No Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days > 0,
  "Past Due",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -78
  && _Days > -1000,
  "Upcoming Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -64
  && _Days >= -77,
  "Danger Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -63,
  "No Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days > 0,
  "Past Due",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 1500000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -92
  && _Days > -1000,
  "Upcoming Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 1500000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days <= -78
  && _Days >= -91,
  "Danger Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days >= -77,
  "No Zone",
  IF (
  'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000
  && 'Sf_Opportunity Line'[Status__c] = "Open"
  && _Days > 0,
  "Past Due","Error")
  )))))))))))))))))))
  - Anonymous
    Not applicable
    4 years ago
    Kumail amitchandak The expected output is the result of the IF statements in the formula. All I'm needing is to know how to incorporate UseRelationship into my VAR statement. I'm thinking it might be the cause of the circular error that I am getting when I try to create a group from this calculated group.
    
    Previously my date table was connected directly to the Opportunity Line table, and now it is connected to the Opportunity table (the header table for Opportunity).
    So, my idea was to try and utilize UseRelationship in my VAR statement to see if that helped.
Kumail
Impactful Individual
4 years ago
Hello
If you could send a sample .pbix that demonstrates what you are looking to get. It would really help to provide you with a quick solution.
You can send the sample .pbix file by adding it to your drive or dropbox and add the link here.
Regards
Kumail Raza