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")
)))))))))))))))))))
