DAX Expressions new

sql-movemedical-prod-useast-01.database.windows.
net
sqldb-bi
COMMENTS IN DAX
Just select line(s) of code you want to comment.
Press Crtl + KC to comment those lines.
Press Ctrl + KU to uncomment commented lines.
****************************************************** Set Context
**********************
Shipped_QTY cc =
VAR line_id = newIntrShip[Order_Line_ID] -- setting context
VAR shipped_till_date= CALCULATE(
SUM(newIntrShip[RSL_Shipped_QTY]),
ALL(newIntrShip), -- setting
context
newIntrShip[Order_Line_Number]=line_id, -- setting
context
newIntrShip[Shipment_Status] in{"PARTIALLY RECEIVED",
"FULLY RECEIVED"}
)
RETURN
shipped_till_date
EU COMMODITY CODE Discrepancy =

var a = RELATED('Item_BasicInfo'[DFF EU Commodity Code])
var b = 'Item_Orginfo'[Org EU Commodity Code]
var c=RELATED(Organization[Organization Code])
var d = SWITCH(TRUE(),
(a=b)||(a<>BLANK() && b=BLANK())||(a=BLANK() && b=BLANK()),"NO",
(a=BLANK() && b<>BLANK()),"YES",
(c IN {"191","415"} ) && (a<>b),"NO","YES")
return d
ECCN Discrepancy =
var a = RELATED('Item_BasicInfo'[DFF ECCN])
var b = 'Item_Orginfo'[Org ECCN]
var c=RELATED(Organization[Organization Code])
var d = SWITCH(TRUE(),
(a=b)||(a<>BLANK() && b=BLANK())||(a=BLANK() && b=BLANK()),"NO",
(a=BLANK() && b<>BLANK()),"YES",
(c IN {"135","191","415"} ) && (a<>b),"NO","YES")
return d
ECCN in Org 135 = CALCULATE(MAX(Item_OrgInfo[Org

ECCN]),FILTER(RELATEDTABLE(Item_OrgInfo),Item_Orginfo[Organization ID]=6780))
ECCN in Org 191 = CALCULATE(MAX(Item_OrgInfo[Org
EU Commodity Code in 191 = CALCULATE(MAX(Item_OrgInfo[Org EU Commodity
Code]),FILTER(RELATEDTABLE(Item_OrgInfo),Item_OrgInfo[Organization ID]=5353))
EU Commodity Code in Org 415 = CALCULATE(MAX(Item_OrgInfo[Org EU Commodity
EU ECCN in Org 415 = CALCULATE(MAX(Item_OrgInfo[Org
Item Status MST = CALCULATE(MAX(Item_OrgInfo[Item
Status]),FILTER(RELATEDTABLE(Item_OrgInfo),Item_BasicInfo[Organization ID]=83))
510k/PMA/Exempt Discrepancy = Switch(True(),

Related(Item_BasicInfo[DFF 510k/PMA/Exempt])<>BLANK() &&
Item_Orginfo[DFF_510k/PMA/Exempt]=BLANK(),"NO",
Related(Item_BasicInfo[DFF 510k/PMA/Exempt])=BLANK() &&
Item_Orginfo[DFF_510k/PMA/Exempt]<>BLANK()
|| (Related(Item_BasicInfo[DFF
510k/PMA/Exempt])<>Item_Orginfo[DFF_510k/PMA/Exempt]),"YES","NO")
COO Discrepancy = Switch(True(),

Related(Item_BasicInfo[DFF Country of Origin])<>BLANK() && Item_Orginfo[ORG
COO]=BLANK(),"NO",
Related(Item_BasicInfo[DFF Country of Origin])=BLANK() && Item_Orginfo[ORG
COO]<>BLANK()
|| (Related(Item_BasicInfo[DFF Country of Origin])<>Item_Orginfo[ORG
COO]),"YES","NO")
Org-Assembly Item ID = Bill_of_Materials[Organization ID] & "-" &

Bill_of_Materials[Assy Item ID]
Invoice Aging Bucket =

IF('Invoice Payments'[Invoice payments Aging] <=0 ,"Within Terms",
IF('Invoice Payments'[Invoice payments Aging] >=1 && 'Invoice Payments'[Invoice
payments Aging]<=7,"00-07 Days" ,
IF('Invoice Payments'[Invoice payments Aging] >=8 && 'Invoice Payments'[Invoice
payments Aging]<=30 ,"08-30 Days" ,
IF('Invoice Payments'[Invoice payments Aging] >30 , "+30 Days", "Aging Error"))))
ECCN Discrepancy =
var a = RELATED(Item_BasicInfo[DFF ECCN]
var b = Item_Orginfo[Org ECCN]
var c = if(Item_Orginfo[Organization ID]=6782,(if(a<>b,"YES"))
return c
ECCN Discrepancy = Switch(True(),

(Item_Orginfo[MST ECCN]=BLANK() && Item_Orginfo[Org ECCN]<>BLANK())||
(IF( NOT ( Item_Orginfo[Organization ID] IN (values( 6782,5353,66782))))
&&
Item_Orginfo[MST ECCN]<>Item_Orginfo[Org ECCN])),"YES","NO")
IF( NOT ( Item_Orginfo[Organization ID] IN

(values( 6782,5353,66782))))
ECCN Discrepancy =
IF(not(
Item_Orginfo[Organization ID] IN{6782,5353,66782} &&
Item_Orginfo[MST ECCN]<>Item_Orginfo[Org ECCN]), "NO",
IF((
Item_Orginfo[Organization ID] IN{6782,5353,66782} &&
Item_Orginfo[MST ECCN]<>Item_Orginfo[Org ECCN]),"YES"))
ECCN Discrepancy =
IF(not( Item_Orginfo[Organization ID] IN{6782,5353,66782} && Item_Orginfo[MST
ECCN]<>Item_Orginfo[Org ECCN]),"YES","NO")
//IF(( Item_Orginfo[Organization ID] IN{6782,5353,66782} && Item_Orginfo[MST
ECCN]<>Item_Orginfo[Org ECCN]),"NO")
Item_Orginfo[MST COO]=BLANK() && Item_Orginfo[ORG COO]<>BLANK()||
Item_Orginfo[MST COO]<>Item_Orginfo[ORG COO],
"YES","NO")
ECCN Discrepancy = Switch(True(),

IF(( Item_Orginfo[Organization ID] IN{6782,5353,66782} && Item_Orginfo[MST
COO]=BLANK() && Item_Orginfo[ORG COO]<>BLANK()) "YES","NO")

EU ECCN in Org 415 = CALCULATE(MAX(Item_OrgInfo[Org


Assigned to Position =
var cntPosition = CALCULATE(COUNTROWS(Control_Functions))
RETURN
IF(cntPosition>0,"Yes","No")
Hold Owner = SWITCH(TRUE(),

'Invoice Holds'[Hold Name] in
{"PO NOT APPROVED","PRICE","QTY ORD","QTY REC","Integra Service PO Hold","INTG Non-
PO Utility Hold","INVALID PO","INTG Signature Approval","QUALITY",
"AMOUNT","NO RATE"},"Business",'Invoice Holds'[Hold Name] in {"PO REQUIRED","LINE
VARIANCE","DIST VARIANCE","Tax Tolerance Hold","Legal Entity Assignment E",
"VENDOR","Tax Shortpay Hold","Tax Process Error Hold","Integra Rejected
Payment","INTG Manual Payment Requi","INTG Banking Hold","INTG Address Change
Hold",
"Tax Tolerance Hold","Tax Determination Hold","TAX VARIANCE"},"AP",
'Invoice Holds'[Hold Name] in {"Integra Stale Dated Check"} , "State Dated Check" ,
'Invoice Holds'[Hold Name] in {"CANNOT EXECUTE ALLOCATION"}, "System" ,'Invoice
Holds'[Hold Name] in {"Unclaimed Property Hold"}, "Unclaimed Property Hold")
Discount Lost & Taken = IF('Invoice Payments'[Discount Taken]=0 && 'Invoice

Payments'[Discount Lost]=0,1,0)
Invoice Aging Bucket = IF('Invoice Payments'[Invoice payments Aging] <=0 ,"Within

Terms", IF('Invoice Payments'[Invoice payments Aging] >=1
&& 'Invoice Payments'[Invoice payments Aging]<=7,"00-07 Days" , IF('Invoice
Payments'[Invoice payments Aging] >=8
&& 'Invoice Payments'[Invoice payments Aging]<=30 ,"08-30 Days" ,IF('Invoice
Payments'[Invoice payments Aging] >30 , "+30 Days", "Aging Error"))))
Payment Method Type Bucketing = SWITCH(TRUE(),Payments[PaymentMethod & TypeFlag] in
{"A--ACH","M--ACH","Q--ACH","M--JPMC SUA","A--JPMC SUA",
"A--MANUAL","A--WIRE","M--WIRE","Q--WIRE","R--WIRE"},"Electronic
Payment",Payments[PaymentMethod & TypeFlag] in {"A--Cheque","A--Cheque",
"A--OUTSOURCED_CHECK","M--OUTSOURCED_CHECK"} ,"US-Check",Payments[PaymentMethod &
TypeFlag] in {"A--INTG_PNC_NET","M--INTG_PNC_NET","Q--INTG_PNC_NET",
"R--INTG_PNC_NET"},"I/C Netting",Payments[PaymentMethod & TypeFlag] in {"M--
MANUAL","Q--MANUAL","R--MANUAL"}, "Wires Manual")
EU Commodity Code in 191 = CALCULATE(MAX(Item_OrgInfo[DFF EU Commodity

(COLUMN)
PO Amount Bucket =
var a = 'Purchase Order Bucket'[USD Amount]
var b = if(a<=1000," 0-1000",

if(a<=5000," 1001-5000",
if(a<=25000," 5001-25000",
if(a<50000,"25001-
50000","50000+"))))
return b
Aging =
if(TODAY()-PurchaseOrder[PO Creation Date]< 120,"00-04 Months",
if(TODAY()-PurchaseOrder[PO Creation Date]<540,"1.0-1.5 Years",
if(TODAY()-PurchaseOrder[PO Creation Date]<730,"1.5-2 Years",
if(TODAY()-PurchaseOrder[PO Creation Date]< 1095,"2-3 Years","over 3
Years")))))))
Opportunity Type =
VAR rslt = SWITCH(TRUE(),
ItemInstance[Remaining Life based on IB Creation Date]<=0,"Capital
Opp.",
ISBLANK(RELATED('Latest Contract'[Contract Number])),"New Contract
Opp.",
RELATED('Latest Contract'[Header Status])="EXPIRED" &&
RELATED('Latest Contract'[Contract Service Type])="WARRANTY","Exp Warranty Cont
Opp.",
RELATED('Latest Contract'[Contract Service Type])="SERVICE","Service Renewal Cont
Opp.",
RELATED('Latest Contract'[Contract Service Type])="WARRANTY", "New
Contract Opp." ,
"Service Renewal Cont Opp."
)
RETURN
rslt
Opportunity Year =
var yr = YEAR(RELATED('Latest Contract'[End Date]))
var curYear = YEAR(TODAY())
return
SWITCH(TRUE(),
ISBLANK(yr),curYear,
MAX(yr,curYear))
\\(c IN {"191","415"} ) && (a<>b),"NO",

\\(NOT(c IN {"191","415"} )) && (a<>b),"YES",
\\(c IN {"191","415"} ) && (a=BLANK() && b<>BLANK()),"YES",
\\(NOT(c IN {"191","415"} )) && (a=BLANK() && b<>BLANK()),"YES",
\\(c IN {"191","415"} ) && (a<>BLANK() && b=BLANK()),"NO",
\\(NOT(c IN {"191","415"} )) && (a<>BLANK() && b=BLANK()),"NO",
\\(c IN {"191","415"} ) && (a=BLANK() && b=BLANK()),"NO",
\\(NOT(c IN {"191","415"} )) && (a=BLANK() && b=BLANK()),"NO","YES")
Invoice Variance FLAG = IF([Invoice Count Difference]=0,"NO","YES")
Measures:
% <2 Days Invoices = DIVIDE([Entered <2 Days Past Recieved Date],[Total Invoices
With Received Dates],0)
% ≥30 Days holds = DIVIDE([≥30 Days holds],Count('Invoice Holds'[Hold Id]),0)
Total Invoices With Received Dates = CALCULATE(count('Invoice Header'[Invoice

ID]),not(ISBLANK('Invoice Header'[Invoice Received Date ])))
Discvount Lost & Taken = IF('Invoice Payments'[Discount Taken]=0 && 'Invoice


Terms",
IF('Invoice Payments'[Invoice payments
Aging] >=1 && 'Invoice Payments'[Invoice payments Aging]<=7,"00-07 Days" ,
IF('Invoice Payments'[Invoice
payments Aging] >=8 && 'Invoice Payments'[Invoice payments Aging]<=30 ,"08-30 Days"
,
IF('Invoice
Payments'[Invoice payments Aging] >30 , "+30 Days", "Aging Error"))))
Invoice payments Aging = DATEDIFF('Invoice Payments'[Due Date],'Invoice

Payments'[Payment Date],DAY)
Total Discount = 'Invoice Payments'[Discount Lost]+'Invoice Payments'[Discount

Taken]
Vendor Num Name = CONCATENATE(CONCATENATE(VendorSite_PO[Vendor

Number],"-"),VendorSite_PO[Vendor Name])
Vendor Num Name = CONCATENATE(CONCATENATE(VendorSite_PO[Vendor

Number],"-"),VendorSite_PO[Vendor Name])
Inspection Days = DATEDIFF(Receipts[Receipt Date],Receipts[Inspected Date],DAY)
Aging =
if(TODAY()-PurchaseOrder[PO Creation Date]<540,"1.0-1.5 Years",
if(TODAY()-PurchaseOrder[PO Creation Date]<730,"1.5-2 Years",
if(TODAY()-PurchaseOrder[PO Creation Date]< 1095,"2-3 Years","over 3
Years")))))))
Balance Amount USD = PurchaseOrder[Amount USD]-PurchaseOrder[Amount Billed USD]
Quantity Remaining = PurchaseOrder[Quantity Ordered]-PurchaseOrder[Quantity

Billed]-PurchaseOrder[Quantity Rejected]
Rejected Amount USD = PurchaseOrder[Quantity Rejected]*PurchaseOrder[Unit

Price]*PurchaseOrder[USD Conversion]
Remaining Open PO Balance USD = IF(PurchaseOrder[Line Closed Code] in

{"CLOSED","FULLY CLOSED"},0,PurchaseOrder[Remaining PO Balance])
Remaining PO Balance = (PurchaseOrder[Unit Price]*PurchaseOrder[Quantity

Remaining])*PurchaseOrder[USD Conversion]
USD Conversion =
var a = LOOKUPVALUE(FxRateToUsd[Conversion Rate],FxRateToUsd[From
Currency],PurchaseOrder[Currency])
var b = if(PurchaseOrder[Currency]="USD",1,a)
return b
PO Amount Bucket =
var b = if(a<=1000," 0-1000",if(a<=5000," 1001-5000",if(a<=25000," 5001-

25000",if(a<50000,"25001-50000","50000+"))))
return b
Count of Holding days =

CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar',WEEKDAY('Calendar'[TheDate],2)<6)
,
DATESBETWEEN('Calendar'[TheDate],'Invoice Holds'[Hold Date],TODAY()))
Hold Aging = if ([Days On Hold] <= 30, "0-30 Days" , if ([Days On Hold] <= 60 ,
"31-60 Days" , if ([Days On Hold] <= 90 , "61-90 Days" ,
if ([Days On Hold]<= 120 , "91-120 Days" , "Over 120 Days"))))
Hold Aging = if ([Days On Hold] <= 30, "0-30 Days" , if ([Days On Hold] <= 60 ,
"31-60 Days" , if ([Days On Hold] <= 90 , "61-90 Days" ,
if ([Days On Hold]<= 120 , "91-120 Days" , "Over 120 Days"))))
Hold Owner = SWITCH(TRUE(),'Invoice Holds'[Hold Name] in {"PO NOT

APPROVED","PRICE","QTY ORD","QTY REC","Integra Service PO Hold","INTG Non-PO
Utility Hold","INVALID PO","INTG Signature Approval","QUALITY","AMOUNT","NO
RATE"},"Business",'Invoice Holds'[Hold Name] in {"PO REQUIRED","LINE
VARIANCE","DIST VARIANCE","Tax Tolerance Hold","Legal Entity Assignment
E","VENDOR","Tax Shortpay Hold","Tax Process Error Hold","Integra Rejected
Payment","INTG Manual Payment Requi","INTG Banking Hold","INTG Address Change
Hold","Tax Tolerance Hold","Tax Determination Hold","TAX VARIANCE"
},"AP",'Invoice Holds'[Hold Name] in {"Integra Stale Dated Check"} , "State Dated
Check" ,'Invoice Holds'[Hold Name] in {"CANNOT EXECUTE ALLOCATION"},
"System" ,'Invoice Holds'[Hold Name] in {"Unclaimed Property Hold"}, "Unclaimed
Property Hold")
Measure = calculate(average('Invoice Holds'[Due Days]),filter('Invoice

Holds','Invoice Holds'[Invoice ID]))
Discount Lost & Taken = IF('Invoice Payments'[Discount Taken]=0 && 'Invoice


Terms", IF('Invoice Payments'[Invoice payments Aging] >=1 && 'Invoice
Payments'[Invoice payments Aging]<=7,"00-07 Days" , IF('Invoice Payments'[Invoice
payments Aging] >=8 && 'Invoice Payments'[Invoice payments Aging]<=30 ,"08-30 Days"
,IF('Invoice Payments'[Invoice payments Aging] >30 , "+30 Days", "Aging Error"))))
Invoice payments Aging = DATEDIFF('Invoice Payments'[Due Date],'Invoice

Payments'[Payment Date],DAY)
Total Discount = 'Invoice Payments'[Discount Lost]+'Invoice Payments'[Discount

Taken]
Invoice Hold Flag = IF(ISBLANK('Invoice Header'[Hold Id]),"NO","YES")
Payment Term Flag = IF('Invoice Distribution'[PO Payment Term]='Invoice

Distribution'[ Invoice Payment Terms],"YES","NO")
PO Payment Term = RELATED(PurchaseOrder[Payment Term])
Amount Limit in USD =

Currency],Control_Group[OU Currency])
var b = if(Control_Group[OU Currency]="USD",1,a)*(Control_Group[Amount Limit In OU
Currency])
Return b
Assigned to Position =
var cntPosition = CALCULATE(COUNTROWS(Control_Functions))
RETURN
IF(cntPosition>0,"Yes","No")
Product Division = RELATED(DCode_GlobalProductRollup[Product Division Code])
Blizzard Modifier Flag =

VAR cnt = CALCULATE(COUNTROWS('Discount List Lines'),'Discount List
Lines'[Product Division]="BLIZZARD")
RETURN
IF(cnt>0,"Y","N")
BillTo matches EndDest = IF('Bill To Country'[Bill To Country]='Bill To

Country'[End Destination Country],1,0)
End Destination Country =

var edc = RELATED(EndDestCountryException[Country Code 2-Digit])
RETURN
IF(NOT(ISBLANK(edc)),edc,'Bill To Country'[Bill To Country])
PaymentMethod & TypeFlag = CONCATENATE(Payments[Payment Type

Flag],CONCATENATE("--",Payments[Payment Method]))

CONCAT([sll].JE_Header_Id, '-' ,[sll].GL_line_number) AS "JE Header - Line
Number",
--------------------------------------------
Transaction Date No timestamp = DATE(YEAR(JobOperationMoveTransaction[Transaction
Date]),MONTH(JobOperationMoveTransaction[Transaction
Date]),DAY(JobOperationMoveTransaction[Transaction Date]))
--------------------------------------------------
Modifier Line Creation Year = YEAR('Discount List Lines'[Line Creation Date])
-------------------------------------------------------------------------------
Mansfield Equipment =
switch(true(),
JobOperationMoveTransaction[From Operation Description]="L32 LATHE"
&& JobOperationMoveTransaction[From Intra Operation Step Type]="QUEUE"
&& RELATED(Job_Assembly[Assembly]) in {"260122001"}, "lathe",
"tEST")
L32 = CALCULATE(sum(JobOperationMoveTransaction[Primary
Quantity]),filter(Job_Assembly,Job_Assembly[Assembly] in
{"260122001","260122003"}),filter(JobOperationMoveTransaction,JobOperationMoveTrans
action[From Operation Description]="L32
LATHE"),FILTER(JobOperationMoveTransaction,JobOperationMoveTransaction[From Intra
Operation Step Type]="QUEUE"))
PM
MEASURE
Latest Completion Date = MAX('Preventive_WO'[Completion Date])
Delay Jobs % = DIVIDE('_Measures'[Count of Delay WO], '_Measures'[Count of

Preventive WO])
Count of WO = '_Measures'[Count of Preventive WO] + '_Measures'[Count of Corrective

WO]
Count of Preventive WO = DISTINCTCOUNT('Preventive_WO'[Work Order Number])
Count of Delay WO = CALCULATE(COUNT('Preventive_WO'[Work Order

Number]),'Preventive_WO'[Delay?] = "Yes")
Count of Corrective WO = DISTINCTCOUNT('Corrective WO'[Work Order Number])

Count of Asset ID = DISTINCTCOUNT('Asset'[Asset ID])
Corrective Jobs % = DIVIDE('_Measures'[Count of Corrective WO],'_Measures'[Count of

WO])
column
Last PM Date = CALCULATE(MAX('Preventive_WO'[Completion Date]))
Next PM Date = CALCULATE(MIN('Preventive_WO'[Scheduled Completion
Date]),'Preventive_WO'[Status]="Released")
Active? = IF((ISBLANK('Asset'[Activity End Date]) && NOT(ISBLANK('Asset'[Activity
Start Date]))), "Yes",IF(ISBLANK('Asset'[Activity End Date]),"Unknown","No"))
Active? = IF((ISBLANK('PM Schedule'[To Effective Date])&&NOT(ISBLANK('PM
Schedule'[From Effective Date]))), "Yes",IF(ISBLANK('PM Schedule'[From Effective
Date]),"Unknown","No"))
Shutdown Leadtime (MTTR) = DATEDIFF('Preventive_WO'[Shutdown End
Date],'Preventive_WO'[Actual Start Date],DAY)
Technician =
VAR wip_entity_id = 'Preventive_WO'[WIP Entity ID]
RETURN
CALCULATE(MAX('Operations'[User Name]), FILTER('Operations', 'Operations'[WIP
Entity ID] = wip_entity_id))
Inspection Due =
switch(true(),
'Preventive WO'[Work Order Number] <>BLANK()
&& 'Preventive WO'[Status]="Released" , "Yes","No")
Inspection Due =
switch(true(),
Preventive_WO[Work Order Number] <>BLANK() &&
Preventive_WO[Status]="Released" && ISBLANK('PM Schedule'[To Effective Date])))
Inspection Due = Switch(True(),

IF(( Preventive_WO[Work Order Number] <>BLANK() && Preventive_WO[Status]="Released"
&& Item_Orginfo[MST COO]=BLANK())) "YES","NO")
Active? = IF(
(
ISBLANK('Asset'[Activity End Date])
&&
NOT(
ISBLANK('Asset'[Activity Start Date])
)), "Yes",IF(ISBLANK('Asset'[Activity End Date]),"Unknown","No"))
Active1? = IF(
(
ISBLANK(Preventive_WO[Work Order Number])
&&
NOT(
ISBLANK(Filter(QA_Discrepancy,QA_Discrepancy[Work_Order])
))
&& Preventive_WO[Status]="Released"),"Yes","No"))
Due? =
var a = Preventive_WO[Status]
var b =Preventive_WO[Work Order Number]
var c = Filter(QA_Discrepancy,QA_Discrepancy[Work_Order])
var d = if(a="Released" && c = BLANK()), "YES","NO"
Return d
Component Consumption Difference % =

VAR qty_required = CALCULATE(SUM( ComponentRequirement[Quantity Required] ))
VAR qty_issued = CALCULATE(SUM( ComponentRequirement[Quantity Issued] ))
RETURN
DIVIDE( qty_issued - qty_required, qty_required, 1)
Job Quantity Completed = CALCULATE(SUM(Job[Quantity Completed]))
Resource Time Efficiency = DIVIDE(

SUM(WIPTransactionsSummary[Prorated Scheduled Units]),
sum(WIPTransactionsSummary[Primary Qty])
)
USD Conversion =
Currency],Payments[Currency])
var b = if(Payments[Currency]="USD",1,a)
return b
USD Amount = Payments[Amount]*Payments[USD Conversion]
PO Amount Bucket =
var b = if(a<=1000," 0-1000",if(a<=5000," 1001-5000",if(a<=25000," 5001-

25000",if(a<50000,"25001-50000","50000+"))))
return b
Aging =
if(TODAY()-PurchaseOrder[Creation Date]< 120,"00-04 Months",
if(TODAY()-PurchaseOrder[Creation Date]<540,"1.0-1.5 Years",
if(TODAY()-PurchaseOrder[Creation Date]<730,"1.5-2 Years",
if(TODAY()-PurchaseOrder[Creation Date]< 1095,"2-3 Years","over 3
Years")))))))
Amount USD = PurchaseOrder[USD Conversion]*PurchaseOrder[Ext Amount]
Avg$/Pos = DIVIDE(SUM(PurchaseOrder[Amount
USD]),CALCULATE(DISTINCTCOUNT(PurchaseOrder[Po Header Id])))
Ext Amount = IF(PurchaseOrder[Quantity Ordered]=blank(),0,PurchaseOrder[Quantity

Ordered]*PurchaseOrder[Unit Price])
Vendor Num Name = CONCATENATE(CONCATENATE(PurchaseOrder_VendorSite[Vendor

Number],"-"),PurchaseOrder_VendorSite[Vendor Name])
Category_UNSPC Code = 'UNSPC Code Categories'[Category] & "(" & 'UNSPC Code
Categories'[UNSPC Code] & ")"
Shortage =
VAR onhand = CALCULATE(SUM(ComponentItems[Nettable Onhand]))
VAR demand = CALCULATE(SUM(JobComponentRequriements[Quantity Open]))
RETURN
IF(demand>onhand,demand-onhand,BLANK())
In Receiving = CALCULATE(SUM(Inventory[Primary Quantity]),Inventory[Stock

Category]="Receiving")
In Transit = CALCULATE(SUM(Inventory[Primary Quantity]),Inventory[Stock

Category]="In Transit")
Nettable Onhand = CALCULATE(SUM(Inventory[Primary Quantity]),Inventory[Stock

Category]="In Stock", Inventory[Nettable - NonNettable]="Nettable")
Open PO Quantity = RELATED(ComponentOpenPO[Open PO Quantitiy])
Last Op Department = CALCULATE(max(JobOperation[Department]),

FILTER(RELATEDTABLE(JobOperation),
JobOperation[Operation Sequence]=Job[Last Op
Seq])
Last Op Description = CALCULATE(max(JobOperation[Operation Description]),

JobOperation[Operation Sequence]=Job[Last Op
Seq])
)
Last Op Seq = CALCULATE(max(JobOperation[Operation Sequence]),

JobOperation[Quantity in Queue]>0 ||
JobOperation[Quantity Waiting to Move]>0 || JobOperation[Quantity Rejected]>0)
)
OP Completion date = Calculate(MAX(JobOperation[Operation Completion
Date]),Filter(RELATEDTABLE(JobOperation),JobOperation[Quantity Running]>0))
First Queue In Date =

VAR wipEntityID = CALCULATE(MIN(JobOperation[WIP Entity ID]))
VAR opSeq = CALCULATE(MIN(JobOperation[Operation Sequence]))
VAR minOperation = CALCULATE(MIN(JobOperation[Operation
Sequence]),FILTER(ALL(JobOperation),JobOperation[WIP Entity ID]=wipEntityID))
RETURN
SWITCH(TRUE(),
opSeq=minOperation,RELATED(Job[Date Released]),
CALCULATE(MIN(JobOperationMoveTransaction[Transaction
Date]),JobOperationMoveTransaction[To Opration Seq]=EARLIER(JobOperation[Operation
Sequence]),JobOperationMoveTransaction[To Intra Operation Step Type]="Queue")
)
Last Move Out Date = CALCULATE(max(JobOperationMoveTransaction[Transaction

Date]),JobOperationMoveTransaction[From Operation
Seq]=EARLIER(JobOperation[Operation Sequence]) )
EU Commodity Code in 191 = CALCULATE(MAX(Item_OrgInfo[DFF EU Commodity

Active_customer_count = CALCULATE(
DISTINCTCOUNT(CustomerHeader[Customer Account Id]),
FILTER(CustomerHeader,CustomerHeader[Cust Account Status]="Active")
)
total_customers_count = CALCULATE(DISTINCTCOUNT(CustomerHeader[Customer Account

Id]),ALL(CustomerHeader))
Zero Cost Item = IF('Item Cost Comparison'[Item Cost In

LocalCurrency]>0,"NO","YES")
ECCN in Org 135 = CALCULATE(MAX(Item_OrgInfo[DFF


510k/PMA/Exempt Discrepancy = Switch(True(),

Related(Item_BasicInfo[DFF 510k/PMA/Exempt])<>BLANK() &&
Item_Orginfo[DFF_510k/PMA/Exempt]=BLANK(),"NO",
Related(Item_BasicInfo[DFF 510k/PMA/Exempt])=BLANK() &&
Item_Orginfo[DFF_510k/PMA/Exempt]<>BLANK()
|| (Related(Item_BasicInfo[DFF
510k/PMA/Exempt])<>Item_Orginfo[DFF_510k/PMA/Exempt]),"YES","NO")

Related(Item_BasicInfo[DFF Country of Origin])<>BLANK() && Item_Orginfo[ORG
COO]=BLANK(),"NO",
Related(Item_BasicInfo[DFF Country of Origin])=BLANK() && Item_Orginfo[ORG
COO]<>BLANK()
|| (Related(Item_BasicInfo[DFF Country of Origin])<>Item_Orginfo[ORG
COO]),"YES","NO")
Month Year Opened = DATE( YEAR( 'Complaints'[Date Opened] ),

MONTH( 'Complaints'[Date Opened] ), "1" )
Date Filter = IF(

SELECTEDVALUE( Incidents[date created] ) >= [N-3 Start Date],
1, 0 )
Incident List of precendented ProductID values =

VAR DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Incidents'[Incident ProductID])
VAR MAX_VALUES_TO_SHOW = 5
RETURN
IF(
DISTINCT_VALUES_COUNT > MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
MAX_VALUES_TO_SHOW,
VALUES( 'Incidents'[Incident ProductID] ),
'Incidents'[Incident ProductID],
ASC
),
", ",
ASC
),
", etc."
),
CONCATENATEX(
VALUES('Incidents'[Incident ProductID]),
", ",
ASC
)
)
List of Precedented Country Code values =

VAR DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Incidents'[Incident Country])
RETURN
IF(
CONCATENATE(
CONCATENATEX(
TOPN(
MAX_VALUES_TO_SHOW,
VALUES( 'Incidents'[Incident Country] ),
'Incidents'[Incident Country],
ASC
),
", ",
ASC
),
", etc."
),
CONCATENATEX(
VALUES('Incidents'[Incident Country]),
", ",
ASC
)
)
List of TW_Product_Family values =

VAR DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Complaints Fields'[TW_Product_Family])
RETURN
IF(
CONCATENATE(
CONCATENATEX(
TOPN(
MAX_VALUES_TO_SHOW,
VALUES('Complaints Fields'[TW_Product_Family]),
'Complaints Fields'[TW_Product_Family],
ASC
),
", ",
ASC
),
", etc."
),
CONCATENATEX(
VALUES('Complaints Fields'[TW_Product_Family]),
", ",
ASC
)
)
N End Date = NOW()
N Incidents = CALCULATE( SUM( Incidents[Count] ),

FILTER( 'Complaints Fields', 'Complaints Fields'[Date_Opened] >=
[N Start Date] )
)
N Sales = CALCULATE( SUM( Sales[Qty] ),
FILTER( Sales, Sales[Year] = YEAR( [N Start Date] ) )
)
N Start Date = DATE( YEAR( NOW() ), 1, 1 )
N-1 End Date = DATE( YEAR( NOW() )-1, 12, 31 )
N-1 Incidents = CALCULATE( SUM( Incidents[Count] ),

[N-1 Start Date] && 'Complaints Fields'[Date_Opened] <= [N-1 End Date])
)
N-1 Sales = CALCULATE( SUM( Sales[Qty] ),

FILTER( Sales, Sales[Year] = YEAR( [N Start Date] ) - 1 )
)
N-1 Start Date = DATE( YEAR( NOW() )-1, 1, 1 )

)

)

)

)
Sales List of ED_Country_Name values =

VAR DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sales'[ED_Country_Name])
RETURN
IF(
CONCATENATE(
CONCATENATEX(
TOPN(
MAX_VALUES_TO_SHOW,
VALUES('Sales'[ED_Country_Name]),
'Sales'[ED_Country_Name],
ASC
),
", ",
ASC
),
", etc."
),
CONCATENATEX(
VALUES('Sales'[ED_Country_Name]),
", ",
ASC
)
)
Sales List of sku values =

VAR DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sales'[sku])
RETURN
IF(
CONCATENATE(
CONCATENATEX(
TOPN(MAX_VALUES_TO_SHOW, VALUES('Sales'[sku]),
'Sales'[sku], ASC),
'Sales'[sku],
", ",
'Sales'[sku],
ASC
),
", etc."
),
CONCATENATEX(VALUES('Sales'[sku]), 'Sales'[sku], ", ", 'Sales'[sku],
ASC)
)
Selected Incident SKU = "FILTERS CURRENTLY APPLIED TO THIS PAGE (from above): " &
UNICHAR(10) & "Selected SKU: " & [Incident List of precendented ProductID values]
Selected Incident SKU & Countries = "FILTERS CURRENTLY APPLIED TO THIS PAGE (from
above): " & UNICHAR(10) & "Selected SKU: " & [Incident List of precendented
ProductID values] & UNICHAR(10) & "Selected Countries: " & [List of Precedented
Country Code values]
Selected Sales Country = "sold in: " & [Sales List of ED_Country_Name values]
Selected Sales SKU = "FILTERS CURRENTLY APPLIED TO THIS PAGE (from above):" &
UNICHAR(10) & "Selected SKU: " & [Sales List of sku values]
Selected Sales SKU & Country = "FILTERS CURRENTLY APPLIED TO THIS PAGE (from
above):" & UNICHAR(10) & "Selected SKU: " & [Sales List of sku values] &
UNICHAR(10) & "Sold In: " & [Sales List of ED_Country_Name values]
Selected TW Product Family = "FILTERS APPLIED TO THIS PAGE (from above): " &
UNICHAR(10) & "TW Product Family: " & [List of TW_Product_Family values]
Selected TW Product Family& Country = "FILTERS APPLIED TO THIS PAGE (from above): "
& UNICHAR(10) & "TW Product Family: " & [List of TW_Product_Family values] &
UNICHAR(10) & "Countries: " & [List of Precedented Country Code values]
Alpha-2 code = IF( ISBLANK( RELATED('TW Country of Incident'[Alpha-2 code]) ),

RELATED('Manual Country'[Alpha-2 code]), RELATED('TW Country of Incident'[Alpha-2
code]) )
CountryName = IF( ISBLANK( RELATED('TW Country of Incident'[Country]) ),

RELATED('Manual Country'[Country]), RELATED('TW Country of Incident'[Country]) )
distinctcountryid = VALUES(CountryID[ID])
FollowUp Number = CALCULATE( SUM( 'Follow Ups'[Count] ),

FILTER( 'Follow Ups', 'Follow Ups'[Complaint_ID] =
EARLIER( 'Follow Ups'[Complaint_ID] ) ),
FILTER( 'Follow Ups', 'Follow
Ups'[Intial_Filing_ID] = EARLIER( 'Follow Ups'[Intial_Filing_ID] ) ),
FILTER( 'Follow Ups', 'Follow Ups'[FollowUp_ID] <=
EARLIER( 'Follow Ups'[FollowUp_ID] ) )
)
Recommended MIR Dev Code = RELATED('MDR-MIR Device Problem Codes 2'[IMDRF Code])
Recommended MIR Eval Code = RELATED('MDR-MIR Evaluation Result Codes 2'[IMDRF

Code])
EEA+CH+TR = IF( Incidents[Incident Country] IN

{"AT", "BG", "CH",
"BE", "CY", "CZ",
"DE", "DK", "EE",
"ES", "FI", "FR",
"GB", "GR", "HU",
"IE", "IS", "IT",
"LI", "LT", "LU",
"LV", "MT", "NL",
"NO", "PL", "PT",
"RO", "SE", "SI",
"SK", "TR"}
, 1, 0 )
Incident Country =
--This is precedented
CALCULATE( MAX( Incidents_Precedence[Precedented Country] ),
FILTER(
ADDCOLUMNS( Incidents_Precedence,
"ComplaintID333", CALCULATE(
MAX( Incidents_Precedence[ComplaintID] ),
FILTER( Incidents_Precedence, Incidents_Precedence[ComplaintID] =

EARLIER( Incidents_Precedence[ComplaintID] ) )
---add more filters in here to refine as we did in precedented precedented

incidents PRID...
)
),
[ComplaintID333] =
Incidents[pr_id]
)
)
Incident CountryName =
CALCULATE( MAX( Incidents_Precedence[Precedented CountryName] ),

FILTER(


incidents PRID...
)
),
[ComplaintID333] =
Incidents[pr_id]
)
)
Incident IMDR Device Code =

CALCULATE( MAX( Incidents_Precedence[Precedented IMDR Device Code] ),

FILTER(

---add more filters in here to refine as we did in precedented incidents PRID...
)
),
[ComplaintID333] =
Incidents[pr_id]
)
)
Incident IMDR Investigation Code =

CALCULATE( MAX( Incidents_Precedence[Precedented IMDR Investigation Code] ),

FILTER(


incidents PRID...
)
),
[ComplaintID333] =
Incidents[pr_id]
)
)
Incident MW Device Code =

CALCULATE( MAX( Incidents_Precedence[Precedented MW Device Code] ),

FILTER(


incidents PRID...
)
),
[ComplaintID333] =
Incidents[pr_id]
)
)
Incident MW Investigation Code =

CALCULATE( MAX( Incidents_Precedence[Precedented MW Investigation Code] ),

FILTER(


incidents PRID...
)
),
[ComplaintID333] =
Incidents[pr_id]
)
)
Incident ProductID =
CALCULATE( MAX( Incidents_Precedence[Precedented ProductID] ),

FILTER(


incidents PRID...
)
),
[ComplaintID22222] =
Incidents[pr_id]
)
)
--this one is cleanear as it evaluates the inner context rather than simply
compounding CALCULATES on top of one another
Country = RELATED( Country[Alpha-2 code] )
CountryName = RELATED(Country[CountryName])
IMDR Device Code Converted From MDR =

RELATED( CODES[IMDRDeviceCodeConvertedFromMDR] )
IMDR Device Code Direct = RELATED( CODES[IMDR Device Code] )
IMDR Investigation Code Converted From MDR =

RELATED(CODES[IMDRInvestFindingCodeConvertedFromMDR])
Precedented Country = CALCULATE(

MAX( Incidents_Precedence[Country] ),
FILTER( Incidents_Precedence,
Incidents_Precedence[ComplaintID] = EARLIER( Incidents_Precedence[ComplaintID] )
),
Incidents_Precedence[Country] <> BLANK() ),
Incidents_Precedence[Precedence] =
CALCULATE( MAX(Incidents_Precedence[Precedence]),
FILTER( 'Incidents_Precedence',
Incidents_Precedence[ComplaintID] = EARLIER
(Incidents_Precedence[ComplaintID]) ),
FILTER( Incidents_Precedence, Incidents_Precedence[Country] <> BLANK() ) )

))
Precedented Country PR = CALCULATE(

MAX( Incidents_Precedence[ID] ),
),
FILTER( 'Incidents_Precedence', Incidents_Precedence[ComplaintID] = EARLIER


))
recedented CountryName = CALCULATE(

MAX( Incidents_Precedence[CountryName] ),
),

))
Precedented IMDR Device Code =

VAR Direct = CALCULATE(
MAX( Incidents_Precedence[IMDR Device Code Direct] ),
EARLIER( Incidents_Precedence[ComplaintID] ) ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Device Code
Direct] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[Precedence] =
Incidents_Precedence[ComplaintID] =
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Device Code Direct] <>
BLANK() ) ))
)
VAR Indirect = CALCULATE(
MAX( Incidents_Precedence[IMDR Device Code Converted From MDR] ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Device Code
Converted From MDR] <> BLANK() ),
Incidents_Precedence[Precedence_For_ProductID] =
CALCULATE( MAX(Incidents_Precedence[Precedence_For_ProductID]),

),
Incidents_Precedence[IMDR Device Code Converted From MDR] <> BLANK() ) ))

)
RETURN
IF( NOT( ISBLANK( Direct ) ), Direct, Indirect )
Precedented IMDR Device Code PR =

var direct = CALCULATE(
),
Incidents_Precedence[IMDR Device Code Direct] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Device Code Direct] <>
BLANK() )
)) )
var indirect = CALCULATE(
),
Incidents_Precedence[IMDR Device Code Converted From MDR] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Device Code Converted From
MDR] <> BLANK() )
)) )
return
if( NOT( ISBLANK( direct ) ), direct, indirect )
Precedented IMDR Investigation Code =

var Direct = CALCULATE(
MAX( Incidents_Precedence[IMDR Investigation Code Direct]
),
),
Incidents_Precedence[IMDR Investigation Code Direct] <> BLANK() ),
Incidents_Precedence[ComplaintID] = EARLIER(Incidents_Precedence[ComplaintID]) ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Investigation Code Direct]
<> BLANK()
) ))
)
var Indirect = CALCULATE(
MAX( Incidents_Precedence[IMDR Investigation Code
Converted From MDR] ),
),
Incidents_Precedence[IMDR Investigation Code Converted From MDR] <> BLANK() ),
Incidents_Precedence[ComplaintID] = EARLIER(Incidents_Precedence[ComplaintID]) ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Investigation Code
Converted From MDR]
<> BLANK() ) ))
)
return
if( not( isblank( Direct ) ), Direct, Indirect )
Precedented IMDR Investigation Code PR =

var direct = CALCULATE(
),
Incidents_Precedence[IMDR Investigation Code Direct] <> BLANK() ),
FILTER( 'Incidents_Precedence', Incidents_Precedence[ComplaintID] =

EARLIER(Incidents_Precedence[ComplaintID]) ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Investigation Code Direct]
<> BLANK() ) ) )
)
var indirect = CALCULATE(
),
Incidents_Precedence[IMDR Investigation Code Converted From MDR] <> BLANK() ),
FILTER( 'Incidents_Precedence', Incidents_Precedence[ComplaintID] =

EARLIER(Incidents_Precedence[ComplaintID]) ),
FILTER( Incidents_Precedence, Incidents_Precedence[IMDR Investigation Code
Converted From MDR] <> BLANK() ) ) )
)
return
if( not(isblank(direct) ), direct, indirect )
Precedented MW Device Code = CALCULATE(
MAX( Incidents_Precedence[MW Device Code]
),
),
Incidents_Precedence[MW Device Code] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[MW Device Code] <>

BLANK() ) )
))
Precedented MW Device Code PR = CALCULATE(

),
Incidents_Precedence[MW Device Code] <> BLANK() ),
Incidents_Precedence[MW Device Code] <> BLANK() ) )
))
Precedented MW Investigation Code = CALCULATE(

MAX( Incidents_Precedence[MW Investigation
Code] ),
),
Incidents_Precedence[MW Investigation Code] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[MW Investigation Code] <>

BLANK() ) )
))
Precedented MW Investigation Code PR = CALCULATE(

),
Incidents_Precedence[MW Investigation Code] <> BLANK() ),

FILTER( Incidents_Precedence, Incidents_Precedence[MW Investigation Code] <>

BLANK() ) )
))
Precedented ProductID = CALCULATE(

MAX( Incidents_Precedence[ProductID] ),
),
Incidents_Precedence[ProductID] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[ProductID] <> BLANK() ) )

))
Precedented ProductID PR = CALCULATE(

),
Incidents_Precedence[ProductID] <> BLANK() ),
FILTER( Incidents_Precedence, Incidents_Precedence[ProductID] <> BLANK() ) )

))
EEA CH TR = IF(
(
Sales[ctryabbrev] IN {"CH", "TR"} ||
Sales[ctryabbrev] IN { RELATED( 'EU Codes (2)'[ISO 2 digit] )
}
)
&& Sales[ctryabbrev] <> BLANK(), 1, 0
)
ILP Type =
var WarrantyStartDate = RELATED(ItemInstance[Warranty Start Date])
var DaysBetweenILPWarranty = DATEDIFF(WarrantyStartDate,Contract[Contract Creation
Date],DAY)
return
IF (Contract[Service Type]<>"Service",
BLANK(),
IF (ISBLANK(WarrantyStartDate),
"No Warranty",
IF (DaysBetweenILPWarranty<=90,
"POS with Capital",
IF (DaysBetweenILPWarranty>90 && DaysBetweenILPWarranty <365,
"POS",
IF (DaysBetweenILPWarranty>365 && DaysBetweenILPWarranty <7*365,
"Renewal")
))))
Most Recent Contract Flag =

var maxStartDt = CALCULATE(MAX(Contract[Start
Date]),FILTER(ALL(Contract),Contract[Instance ID]=EARLIER(Contract[Instance ID])
&& Contract[Contract Service Type]="SERVICE"
&& Contract[Line Status] <>"TERMINATED"
&& Contract[Line Status] <>"CANCELLED"
))
RETURN
SWITCH(TRUE(),
Contract[Contract Service Type]="WARRANTY",BLANK(),
maxStartDt=Contract[Start Date] && Contract[Line Status] <>"TERMINATED" &&
Contract[Line Status]<>"CANCELLED","Y",
"N")
Most Recent Flag =

var maxCreationDate = CALCULATE(MAX(Contract[Contract Creation
))
RETURN
SWITCH(TRUE(),
maxCreationDate=Contract[Contract Creation Date] && Contract[Line Status]
<>"TERMINATED" && Contract[Line Status]<>"CANCELLED","Y",
"N")
Most Recent Warranty Flag =
var maxStartDt = CALCULATE(MAX(Contract[Start
&& Contract[Contract Service Type]="WARRANTY"
))
RETURN
SWITCH(TRUE(),
Contract[Contract Service Type]="SERVICE",BLANK(),
maxStartDt=Contract[Start Date] && Contract[Line Status] <>"TERMINATED" &&
Contract[Line Status] <>"CANCELLED","Y",
"N")
Contract# = CONVERT(Contract_Billing[Contract_Number],STRING)
FS Actual Expense Charges = CALCULATE(SUM(FieldServiceActuals[Amount Submitted]),

FieldServiceActuals[Billing Type]="Expense")
FS Actual Labor Charges = CALCULATE(SUM(FieldServiceActuals[Amount Submitted]),

FieldServiceActuals[Billing Type]="Labor")
FS Actual Material Charges = CALCULATE(SUM(FieldServiceActuals[Amount Submitted]),

FieldServiceActuals[Billing Type]="Material")
FS Actual Total Charges = SUM(FieldServiceActuals[Amount Submitted])
Potential Expense Charges = CALCULATE(SUM(FieldServiceActuals[Potential Charges]),

FieldServiceActuals[Billing Type]="Expense")
Potential Labor Charges = CALCULATE(SUM(FieldServiceActuals[Potential Charges]),

FieldServiceActuals[Billing Type]="Labor")
Potential Material Charges = CALCULATE(SUM(FieldServiceActuals[Potential Charges]),

FieldServiceActuals[Billing Type]="Material")
Opportunity Type =
ItemInstance[Remaining Life based on IB Creation Date]<=0,"Capital
Opp.",
ISBLANK(RELATED('Latest Contract'[Contract Number])),"New Contract
Opp.",
RELATED('Latest Contract'[Contract Service Type])="WARRANTY","Exp Warranty Cont
Opp.",
RELATED('Latest Contract'[Contract Service Type])="SERVICE","Service Renewal Cont
Opp.",
RELATED('Latest Contract'[Contract Service Type])="WARRANTY", "New
Contract Opp." ,
"Service Renewal Cont Opp."
)
RETURN
rslt
Opportunity Year =
var yr = YEAR(RELATED('Latest Contract'[End Date]))
var curYear = YEAR(TODAY())
return
SWITCH(TRUE(),
ISBLANK(yr),curYear,
MAX(yr,curYear))
Recent ILP End Date = CALCULATE(max(Contract[End Date]),

FILTER(RELATEDTABLE(Contract),
UPPER(Contract[Service Type])="SERVICE"
&& NOT Contract[Line Status] IN
{"CANCELLED","TERMINATED"}
))
Recent ILP Number = CALCULATE(max(Contract[Contract Number]),

&& Contract[Start Date] =
EARLIER(ItemInstance[Recent ILP Start Date])
))
Recent ILP Start Date = CALCULATE(max(Contract[Start Date]),

))
Remaining Life based on IB Creation Date = 8*365 - DATEDIFF(ItemInstance[Instance

Creation Date],TODAY(),DAY)
Remaining Service Life = if(ISBLANK(ItemInstance[Warranty Start Date]),

BLANK(),
7*365 - DATEDIFF(ItemInstance[Warranty Start Date],TODAY(),DAY)
)
Renewal Opportunity =
if(ItemInstance[Remaining Service Life]>0 && ItemInstance[Recent ILP End
Date]<=TODAY()
,"Yes",
"No"
)
Repair Charge Last Currency = CALCULATE(MAX(RepairOrderEstimates[Currency Code]))
Total Repair Actual Charges = CALCULATE(SUM(RepairOrderActuals[Charge]))
Warranty Expiry Date = CALCULATE(max(Contract[End Date]),

UPPER(Contract[Service
Type])="WARRANTY"
))
Warranty Start Date = CALCULATE(max(Contract[Start Date]),
UPPER(Contract[Service
Type])="WARRANTY"
))
Latest Contract = FILTER(Contract,Contract[RANK_NUM]=1)
Most Recent Service Contract = FILTER(ALLNOBLANKROW(Contract),Contract[Most Recent

Contract Flag]="Y")
Most Recent Warranty = FILTER(ALLNOBLANKROW(Contract),Contract[Most Recent Warranty

Flag]="Y")
Complaint Flag (SR & RO Combined) =

IF(RELATED(ServiceRequest[SR Type])="Complaint" || RepairOrder[SR
Type]="Complaint" ||
LEFT(RELATED(ServiceRequest[Complaint]),3)="YES","Complaint" ,BLANK())
Duration Since Loaner Shipped = DATEDIFF(RepairOrder[Loaner Ship

Date],IF(ISBLANK(RepairOrder[Loaner Receipt Date]),TODAY(),RepairOrder[Loaner
Receipt Date]),DAY)
Duration since Product Receipt = if(ISBLANK(RepairOrder[RMA Ship Date]),

DATEDIFF(RepairOrder[Physical Receipt Date],TODAY(),DAY),
DATEDIFF(RepairOrder[Physical Receipt Date],RepairOrder[RMA
Ship Date],DAY))
Ghost Repair Order Flag =

var remaingWork =
CALCULATE(COUNTROWS(RepairOrder_Task),NOT(RepairOrder_Task[Task Status] IN
{"Closed","Close","Completed","Cancelled"}))
var assignedTaskCount =
CALCULATE(COUNTROWS(RepairOrder_Task),FILTER(RepairOrder_Task,
RepairOrder_Task[Repair Line ID] = RepairOrder[Repair Line ID] &&
(RepairOrder_Task[Task Status] IN {"Accepted","Assigned","On hold","Working"} ||
(RepairOrder_Task[Task Status]="Open" && NOT(ISBLANK(RepairOrder_Task[Resource

Name]))))
))
RETURN
SWITCH(TRUE(),
RepairOrder[Repair Status]="Open" && remaingWork>0 &&
(assignedTaskCount=0 || ISBLANK(assignedTaskCount)),"Yes",
"No")
Latest Diagnostics =
SUMMARIZE(FILTER(RELATEDTABLE(RepairOrderDiagnosticCodes),RepairOrderDiagnosticCode
s[RANK]=1),RepairOrderDiagnosticCodes[Diagnostic Code])
Latest Serice Code =

SUMMARIZE(FILTER(RELATEDTABLE(RepairOrderServiceCodes),RepairOrderServiceCodes[RANK
]=1),RepairOrderServiceCodes[Service Code Name])
Loaner in Field = IF(RepairOrder[Loaner Ship Date] <> BLANK() && RepairOrder[Loaner

Receipt Date] = BLANK(),"Yes","No")
Loaner Receipt Date = CALCULATE(max(RepairOrderTransactions[Actual Shipment Date]),

FILTER(all(RepairOrderTransactions),
RepairOrderTransactions[Action Type]="RMA" &&
RepairOrderTransactions[Action Code]="LOANER" &&
RepairOrderTransactions[Prod Txn Status] = "RECEIVED" &&
RepairOrderTransactions[Repair Line Id]=EARLIER(RepairOrder[Repair Line
ID])
))
Loaner Serial Number = CALCULATE(max(RepairOrderTransactions[Source Serial

Number]),
RepairOrderTransactions[Action Type]="SHIP" &&
RepairOrderTransactions[Prod Txn Status]<>"CANCELLED" &&
ID])
))
Loaner Ship Date = CALCULATE(max(RepairOrderTransactions[Actual Shipment Date]),

RepairOrderTransactions[Prod Txn Status] = "SHIPPED" &&
ID])
))
Metric-In Evaluation Days =

DATEDIFF(RepairOrder[WaitingForEvaluation Status
Date],RepairOrder[Evaluation Date],DAY)
Metric-In Transit Days = DATEDIFF(RELATED(ServiceRequest[SR

Date]),RepairOrder[Physical Receipt Date],DAY)
Metric-Time to Repair Days =

DATEDIFF(RepairOrder[Evaluation Date],RepairOrder[Repair Completion
Date],DAY)
Metric-Waiting for Decontamination Days = DATEDIFF(RepairOrder[Physical Receipt

Date],RepairOrder[Decontamination Date],DAY)
Metric-Waiting for Evaluation Days =

DATEDIFF(RepairOrder[Decontamination
Date],RepairOrder[WaitingForEvaluation Status Date],DAY)
Metric-Waiting for Packaging =

DATEDIFF(RepairOrder[Repair Completion
Date],RepairOrder[WaitingForQuality Status Date],DAY)
Metric-Waiting for Quality SignOff =

DATEDIFF(RepairOrder[WaitingForQuality Status Date],RepairOrder[Quality
Sign-Off Date],DAY)
Metric-Waiting for Shipping Days =

DATEDIFF(RepairOrder[Quality Sign-Off
Date],RepairOrder[WaitingForShipping Status Date],DAY)
RepairInProgress Status Date = CALCULATE(MIN(RepairStausChangeHistory[Event

Date]),RepairStausChangeHistory[To Status Code]="RINP")
RMA Number = CALCULATE(max(RepairOrderTransactions[Sales Order Number]),

RepairOrderTransactions[Action Code]="CUST_PROD" &&
RepairOrderTransactions[Prod Txn Status]<>"CANCELLED" &&
ID])
))
RMA Received Date = CALCULATE(max(RepairOrderTransactions[Actual Shipment Date]),

RepairOrderTransactions[Prod Txn Status] IN {"RECEIVED"} &&
ID])
))
RMA Ship Date = CALCULATE(max(RepairOrderTransactions[Actual Shipment Date]),

RepairOrderTransactions[Prod Txn Status] IN {"SHIPPED","RELEASED"} &&
ID])
))
Shipped Status Date = CALCULATE(MIN(RepairStausChangeHistory[Event

Date]),RepairStausChangeHistory[To Status Code]="ITEMSHP")
SR-Repair Number = RELATED(ServiceRequest[SR Number]) & "-" & RepairOrder[Repair

Number]
WaitingForEvaluation Status Date = CALCULATE(MIN(RepairStausChangeHistory[Event

Date]),RepairStausChangeHistory[To Status Code]="WFE - WAITING EVALUATION")
WaitingForQuality Status Date = CALCULATE(MIN(RepairStausChangeHistory[Event

Date]),RepairStausChangeHistory[To Status Code]="QAWAIT")
WaitingForShipping Status Date = CALCULATE(MIN(RepairStausChangeHistory[Event

Date]),RepairStausChangeHistory[To Status Code]="WSHIP")
Invoice Match Actual =

var actual = SUM(RepairOrderActuals[Charge])
var invoice = [Total Invoice Amount]
RETURN
IF(actual=invoice,"Y","N")
Total Invoice Amount = SUM(RepairOrder_Invoices[Invoice Line Amount])

Actual Expense Charges =
CALCULATE(SUM(RepairOrderActuals[Charge]),RepairOrderActuals[Billing
Type]="Expense")
Actual Labor Charges =

CALCULATE(SUM(RepairOrderActuals[Charge]),RepairOrderActuals[Billing Type]="Labor")
Actual Material Charges =

CALCULATE(SUM(RepairOrderActuals[Charge]),RepairOrderActuals[Billing
Type]="Material")
Total Actual Charges = SUM(RepairOrderActuals[Charge])
Estimate Expense Charges =

CALCULATE(SUM(RepairOrderEstimates[Charge]),RepairOrderEstimates[Billing
Type]="Expense")
Estimate Labor Charges =

Type]="Labor")
Estimate Material Charges =

Type]="Material")
Estimate Material Cost =

CALCULATE(SUM(RepairOrderEstimates[Cost]),RepairOrderEstimates[Billing
Type]="Material")
Total Estimate Charges = SUM(RepairOrderEstimates[Charge])
RevenueRecognition = CROSSJOIN(RevenueRecognitionSummary,RevenueRecogntionPeriods)
RRAmount = if(
RevenueRecognition[LastDayofMonth]>=RevenueRecognition[Start Date] &&
RevenueRecognition[LastDayofMonth]<=RevenueRecognition[End Date],
RevenueRecognition[MonthlyAmount],0
)
LastDayofMonth = ENDOFMONTH(RevenueRecognitionDates[Date])
RevenueRecognitionDates =
CALENDAR(
DATE ( 2019, 1, 1 ),
DATE ( 2025, 12, 1 ))
LastDayofMonth = ENDOFMONTH(RevenueRecognitionDates[Date])
RevenueRecognitionSummary = SUMMARIZE(
FILTER(ALL(Contract),(NOT (Contract[Line Status] IN
{"Expired","Cancelled"} && Contract[Amount]>0)) ) ,
Contract[Operating Unit],
Contract[Contract Number],
ItemInstance_Owner[Owner Name],
Contract[Service Type],
ItemInstance_Item[Item Number],
ItemInstance[Serial Number],
Contract[Line Service Name],
Contract[Start Date],
Contract[End Date],
Contract[Line Status],
"Amount",
sum(Contract[Amount]))
MonthlyAmount =
ROUND(DIVIDE(RevenueRecognitionSummary[Amount],RevenueRecognitionSummary[Months],0)
,2)
Months = DATEDIFF(RevenueRecognitionSummary[Start
Date],RevenueRecognitionSummary[End Date]+1,MONTH)
RevenueRecogntionPeriods = VALUES(RevenueRecognitionDates[LastDayofMonth])
Latest Notes =
CALCULATE(MAX(RepairOrderEstimateNotes[Notes]),RELATEDTABLE(RepairOrderEstimateNote
s))
SR Close Date without timestamp = DATE(YEAR(ServiceRequest[SR Close

Date]),MONTH(ServiceRequest[SR Close Date]),DAY(ServiceRequest[SR Close Date]))
SR Date without timestamp = DATE(YEAR(ServiceRequest[SR

Date]),MONTH(ServiceRequest[SR Date]),DAY(ServiceRequest[SR Date]))
ServiceRequest_ItemInstance = ALL(ItemInstance)
SR Calendar = ALL('Calendar')
SR Close by Date = SUMMARIZE(ServiceRequest,ServiceRequest[SR Close Date without

timestamp],"SR Close Count",COUNT(ServiceRequest[SR Number]))
SR Open By Date = SUMMARIZE(ServiceRequest,ServiceRequest[SR Date without

timestamp],"SR Open Count",COUNT(ServiceRequest[SR Number]))
*********************************
Backordered = IF(Delivery[Released Status Code]="B","Yes","No")
Delivery Creation Datae NoTimeStamp = DATE(YEAR(Delivery[Delivery Creation

Date]),MONTH(Delivery[Delivery Creation Date]),DAY(Delivery[Delivery Creation
Date]))
Ext Requested Amount = RELATED(SalesOrder[Unit Selling Price])*Delivery[Requested

Quantity]
Ext Requested Amount USD = Delivery[Ext Requested Amount] *

IF(RELATED(SalesOrder[Currency]) = "USD",1,RELATED('Fx Rates'[Conversion Rate]))
Ready To Release = IF(Delivery[Released Status Code]="R","Yes","No")
Released To W/H = IF(Delivery[Released Status Code] ="S","Yes","No")
Distinct Lines =
Var Cnt = CALCULATE(DISTINCTCOUNT(SalesOrder[Line ID]))
Return
IF(ISBLANK(Cnt),0,Cnt)
Distinct Order =
Var Cnt = CALCULATE(DISTINCTCOUNT(SalesOrder[Order Number]))
Return
IF(ISBLANK(Cnt),0,Cnt)
Shortage Quantity =
var OQ = SUM(SalesOrder[Ordered Quantity])
var AQ = sum(Onhand[Reservable Onhand])
var SQ= OQ-AQ
Return
SQ
Today's Date = TODAY()
Today's Week Number = WEEKNUM(TODAY(),1)
Aged CS BackOrder =
SWITCH(TRUE(), Related(SalesOrder_TransactionType[Order Type]) IN {
"AT Charge Sheet Order",
"AT Standard Order",
"AU Standard Order",
"BE Product Request",
"CA Sample Order",
"CA Standard Order",
"CH Charge Sheet Order",
"CH Demo Order",
"DE Sales Credit Only Order",
"DE Sales Standard Order",
"DE Standard Order",
"FR Standard Order",
"ILS Bill Only Order",
"ILS Credit Only Order",
"ILS Standard Order",
"JP Sample Order",
"JP Standard Order",
"UK EDC Set Shipment"
}
&& RELATED(SalesOrder[On Hold])="No" && RELATED( SalesOrder[On
Backorder])="Yes" ,1,0)
Aged CS BackOrder > 90 =

SWITCH(TRUE(), Related(SalesOrder[Order Days])>90 &&
Related(SalesOrder_TransactionType[Order Type]) IN {
"CA Sample Order",
"CH Demo Order",
"JP Sample Order",
}
&& RELATED(SalesOrder[On Hold])="No" && RELATED( SalesOrder[On
Backorder])="Yes" ,1,0)
Aged Entered Returns =

SWITCH( TRUE(),
CONTAINSSTRING(Related(SalesOrder_TransactionType[Order Type]) ,"RETURN") &&
RELATED(SalesOrder[Order Status])="ENTERED" && RELATED(SalesOrder[Order
Days])>8,1,0)
Aged Order - Not CS Resp = SWITCH(TRUE(),Related(SalesOrder[Order Days])>90

&& RELATED(SalesOrder_TransactionType[Order Type] ) IN {
"AU Intercompany Repair",
"AU PM Order",
"AU Repair Order",
"CA Intercompany Repair",
"CA Internal Order",
"CA PM Order",
"CA Product Request",
"CA Repair Order",
"CH Mfg Credit Only Order",
"CH Mfg Intercompany Order",
"CH Mfg Repair Order",
"CH Mfg Standard Order",
"CH Repair Order",
"DE Intercompany Order",
"DE Internal Order",
"DE Sales Rental Order",
"FR Mfg Intercompany Order",
"FR Mfg Return Order",
"FR Mfg Standard Order",
"ILS Charge Sheet Order",
"ILS Eval Order",
"ILS Intercompany Order",
"ILS Internal Order",
"ILS Mfg Intercompany Sale",
"ILS Mfg Internal Order",
"ILS Repair Order",
"ILS Rental Order",
"ILS Sales Clinical Sample",
"ILS Sample Order",
"IT Rental Order",
"JPN Rental Order",
"NZ Repair Order",
"UK Rental Order",
"YRK Repair Order"
}
&& Isblank(RELATED(OrderReason[Reason Comment])) &&
Isblank(RELATED(OrderReason[Reason])),1,0)
Aged Order - Not CS Resp > 90 = SWITCH(TRUE(),Related(SalesOrder[Order Days])>90

&& RELATED(SalesOrder_TransactionType[Order Type] ) IN {
"AU PM Order",
"AU Repair Order",
"CA PM Order",
"CA Repair Order",
"CH Repair Order",
"DE Sales Rental Order",
"ILS Eval Order",
"ILS Repair Order",
"ILS Rental Order",
"ILS Sample Order",
"IT Rental Order",
"JPN Rental Order",
"NZ Repair Order",
"UK Rental Order",
"YRK Repair Order"
}
&& Isblank(RELATED(OrderReason[Reason Comment])) &&
Isblank(RELATED(OrderReason[Reason])),1,0)
Aged Order - CS Resp = /*SWITCH(TRUE(), RELATED(SalesOrder[Order Days])>90

&& Related(SalesOrder_TransactionType[Order Type]) IN {
"CA Sample Order",
"CH Demo Order",
"JP Sample Order",
}
&& (ISBLANK(RELATED(OrderReason[Reason Comment])) &&
ISBLANK(RELATED(OrderReason[Reason])) || DATEDIFF(RELATED(OrderReason[Reason
Creation Date]),TODAY(),DAY) >90) ,1,0) */
SWITCH(TRUE(), RELATED(SalesOrder[Order Days])>90
"CA Sample Order",
"CH Demo Order",
"JP Sample Order",
}
Creation Date]),TODAY(),DAY) >90)
&& RELATED(SalesOrder[On Hold])="No" && RELATED(SalesOrder[On
Backorder])="NO",1,0)
Aged Order - CS Resp > 90 = SWITCH(TRUE(), RELATED(SalesOrder[Order Days])>90

"CA Sample Order",
"CH Demo Order",
"JP Sample Order",
}
Creation Date]),TODAY(),DAY) >90) ,1,0)
Aged Order - CS Resp > 90. = SWITCH(TRUE(), RELATED(SalesOrder[Order Days])>90

"CA Sample Order",
"CH Demo Order",
"JP Sample Order",
}
Creation Date]),TODAY(),DAY) >90)
&& RELATED(SalesOrder[On Hold])="No" && RELATED(SalesOrder[On
Backorder])="NO",1,0)
Aged Order - Returns = SWITCH(TRUE(),Related(SalesOrder[Order Days])>90

&& RELATED(SalesOrder_TransactionType[Order Type Category] ) IN {"Return
Order", "Credit Card Returns"},1,0)
Awaiting Export Screening =

SWITCH(TRUE(),
RELATED(SalesOrder[Line Status])="Awaiting_Export_Screening",1,0)
Booked > 24 hours =

Var Hrs=DATEDIFF(RELATED(SalesOrder[Booked Date]),NOW(),HOUR)
Return
SWITCH (
TRUE (),
RELATED ( SalesOrder[Line Status] ) = "Booked"
&& RELATED ( SalesOrder[On Hold] ) = "No"
&& Hrs>24
, 1,
0
)
Cold Chain Exports =

SWITCH (
TRUE (),
RELATED ( SalesOrder[Most Recent Delivery Status] ) = "Staged/Pick Confirmed"
&& RELATED ( SalesOrder_ShipToCustomer[Country Name] ) <> "US"
&& RELATED ( SalesOrder_Items[Item Number] )
IN {
"202010DS",
"202050",
"204003",
"206320",
"206520",
"DSD5005",
"823072",
"NS0343",
"821750",
"821745",
"823073",
"823074"
}, 1,
0
)
Credit Card Approval Code = SWITCH(TRUE(), RELATED(SalesOrder[Payment

Term])="Credit Card"
&& RELATED(SalesOrder_CreditCard_Auth[Auth Code Len])<6 &&
Related( SalesOrder_TransactionType[Order Type])="ILS Standard Order",1,0)
Credit Card Auth Failure =

var cnt=CALCULATE(COUNT(SalesOrder_Holds[Order Line
ID]),FILTER(Holds_Definition,Holds_Definition[Hold Name]="Credit Card Auth
Failure"))
RETURN
SWITCH(TRUE(), cnt>0,1,0)
Doctors Certification Hold =

var cnt= CALCULATE(COUNTROWS(DISTINCT(SalesOrder_Holds[Order Line
ID])),Holds_Definition[Hold Name]="Doctor Certification Hold",
ALLEXCEPT(Order_Exception,Order_Exception[Line ID]))
Return
SWITCH(TRUE(),cnt>0,1,0)
Duplicate =
var od = RELATED(SalesOrder[Order Date])
var PrevOd = PREVIOUSDAY(SalesOrder[Order Date])
var OrdCnt= DISTINCTCOUNT(SalesOrder[Order Number])
Return
1
EDI entered status =

SWITCH(TRUE(), RELATED(SalesOrder[Order Source]) in {"EDIGHX", "EDIGXS",
"EDIGHXTNET" } &&
RELATED(SalesOrder[Order Status])="ENTERED" &&
RELATED(SalesOrder[On Hold])="No",1,0)
Export Compliance Hold =

var
cnt=CALCULATE(COUNTROWS(SalesOrder_Holds),FILTER(Holds_Definition,Holds_Definition[
Hold Name]="Export Compliance Hold"),ALLEXCEPT(Order_Exception,Order_Exception[Line
ID]))
Var res=SWITCH(TRUE(), cnt>=1,1,0)
RETURN
res
Government Order Hold =

ID])),Filter(ALLNOBLANKROW(Holds_Definition[Hold Name]),Holds_Definition[Hold
Name]="Government Order Hold (H)"),
Return
Hold Released and Entered =

//var cnt = CALCULATE(COUNT(SalesOrder_Holds_Released[Order Line ID]))
/* var cnt= CALCULATE(COUNTROWS(DISTINCT(SalesOrder_Holds_Released[Order Line
ID])),ALLEXCEPT(Order_Exception,Order_Exception[Line ID]))
Return
SWITCH(TRUE(), RELATED(SalesOrder_TransactionType[Order Type Category])="Standard
Order"
&& RELATED(SalesOrder[Line Status])="Entered" && RELATED(SalesOrder[On
Hold])="No" && cnt>0,1,0) */
var cnt= CALCULATE(COUNTROWS(DISTINCT(SalesOrder_Holds_Released[Order Line

ID])),ALLEXCEPT(Order_Exception,Order_Exception[Line ID]))
Return
SWITCH(TRUE(), RELATED(SalesOrder_TransactionType[Order Type Category])="Standard
Order"
&& RELATED(SalesOrder[Line Status]) ="Entered" && RELATED(SalesOrder[On
Hold])="No" ,1,0)
Invoice Hold =
/*var cnt= CALCULATE(COUNTROWS(DISTINCT(SalesOrder_Holds[Order Line
ID])),Holds_Definition[Hold Name] IN {"INTG Invoice", "INTG Invoice Interface
Hold(H)", "INTG Invoice Interface Hold(L)", "INTG IT Invoice Interface Hold"},
ALLEXCEPT(Order_Exception,Order_Exception[Line ID])) */
var cnt = CALCULATE(COUNTROWS(DISTINCT(SalesOrder_Holds[Order Line
ID])),CONTAINSSTRING(Holds_Definition[Hold Name] ,"INTG Invoice"),
Return
No Customer PO Price =
SWITCH(TRUE(),ISBLANK(RELATED(SalesOrder[Customer Net Price]))
&& RELATED(SalesOrder_TransactionType[Order Type Category])="Standard Order"
&& RELATED(SalesOrder[Line Status])="Entered"
,1,0)
Obsolete =
var Reserved = RELATED(Onhand[Reserved])
var Nettable = RELATED(Onhand[Nettable Onhand])
var OrderQuantity = RELATED(SalesOrder[Ordered Quantity])
Return
SWITCH (
TRUE (),
RELATED ( SalesOrder_Items[Item Status] ) = "Obsolete"
&& Nettable = 0 && Reserved < OrderQuantity, 1,
0
)
On Hold Released to Warehouse =

VAR cnt =
CALCULATE (
COUNTROWS ( DISTINCT ( SalesOrder_Holds[Order Line ID] ) ),
Holds_Definition[Hold Name] In {"INTG Invoice","INTG Invoice Interface Hold
(L)","INTG Invoice Interface Hold (H)","INTG IT Invoice Interface Hold (L)","IT
Invoice Interface Hold (L)"}
,ALLEXCEPT ( Order_Exception, Order_Exception[Line ID] )
)
VAR RS = CALCULATE(DISTINCTCOUNT(Delivery[Order Line ID]),
Delivery[Released Status]="Released to
Warehouse",ALLEXCEPT(Order_Exception,Order_Exception[Line ID]))
RETURN
SWITCH (
TRUE (),
RS > 0
&& RELATED ( SalesOrder[On Hold] ) = "Yes"
&& cnt = 0, 1,
0
)
Order Not Progressing AWAITING_RECEIPT =

SWITCH (
TRUE (),
RELATED ( SalesOrder[Line Status] ) = "AWAITING_RECEIPT"
&& RELATED ( SalesOrder_TransactionType[Order Type Category] ) = "Standard
Order"
&& RELATED ( SalesOrder_Warehouse[Warehouse Code] )
IN { BLANK(), "100", "115" }
&& ISBlank( RELATED ( SalesOrder[Most Recent Delivery ID] ))-- = BLANK

()
&& ISBlank( RELATED ( SalesOrder[Ship Set] )) --= BLANK ()
//&& Not( RELATED ( DCodeGlobalProductRollup[Rollup Level2] ) =
"Private Label")
&& NOt(RELATED(SalesOrder_SoldToCustomer[Account Number]) IN { "28071",
"95085","31502","33078","100068","32448","32473","32526","10999","32453","32369" })
, 1, 0
)
Order Not Progressing-AWAITING_SHIPPING =

SWITCH (
TRUE (),
RELATED ( SalesOrder[Line Status] ) = "AWAITING_SHIPPING"
&& RELATED ( SalesOrder_TransactionType[Order Type Category] ) = "Standard
Order"
&& RELATED ( SalesOrder[Order Days] ) > 1
&& NOT (
RELATED ( SalesOrder_Warehouse[Warehouse Code] )
IN { "105", "150", "165", "406" }
)
&& ISBlank( RELATED ( SalesOrder[Most Recent Delivery ID] ))-- = BLANK
()
&& ISBlank( RELATED ( SalesOrder[Ship Set] )) --= BLANK ()
&& RELATED ( SalesOrder[On Backorder] ) = "No"
&& RELATED ( SalesOrder[Future Date] ) = "No", 1,
0
)
Priority Orders In Booked statuse =

SWITCH(True, RELATED(SalesOrder[line Status])="Booked" &&
RELATED(SalesOrder[Ship Method Code]) IN { "000001_FEDEX_P_FOV",
"000001_FEDEX_P_IPRS",
"000001_FEDEX_P_SAT","000001_FEDEX_P_STO","000001_UPS_P_1DA","000001_UPS_P_1DM","00
0001_UPS_P_1DP" ,"000001_UPS_P_SAT"}
,1,0)
RDC Exports Not Dropped = SWITCH(TRUE(),
RELATED(SalesOrder_operatingUnit[Operating Unit])="OU United States" &&
RELATED(SalesOrder_Warehouse[Warehouse Code])="120" &&
RELATED(SalesOrder_ShipToCustomer[Ship To Country])<>"US" &&
RELATED(SalesOrder[Most Recent Delivery Status])="Ready to Release" &&
RELATED ( SalesOrder[Line Status] ) = "AWAITING_SHIPPING"
&& RELATED ( SalesOrder[On Backorder] ) = "No"
&& RELATED ( SalesOrder[Future Date] ) = "No", 1,
0
)
Released to Warehouse > 48 Hours =

Var Hrs=//DATEDIFF(RELATED(SalesOrder[Booked Date]),NOW(),HOUR)
DATEDIFF(RELATED(SalesOrder[Most Recent Delivery ID Date]),NOW(),HOUR)
Return
SWITCH (
TRUE (),
RELATED ( SalesOrder[Most Recent Delivery Status] ) in { "Released to
Warehouse"}
&& Hrs>48 && ISBLANK(RELATED(SalesOrder[Ship Set])) &&
RELATED(SalesOrder[Schedule Ship Date])<=TODAY()
, 1,
0
)
Rx License Hold =
ID])),Filter(ALLNOBLANKROW(Holds_Definition[Hold Name]),Holds_Definition[Hold
Name]="INTG RX LICENSE INFO HOLD (L)"),
Return
Ship Only Lines =

SWITCH (
TRUE (),
RELATED ( SalesOrder_TransactionType[Order Type Category] ) = "Standard Order"
&&CONTAINSSTRING( RELATED ( SalesOrder[Line Type] ), "Ship Only")
, 1,
0
)
Staged Picked Confirmed > 48 Hours =

Var Hrs=//DATEDIFF(RELATED(SalesOrder[Booked Date]),NOW(),HOUR)
DATEDIFF(RELATED(SalesOrder[Most Recent Delivery ID Date]),NOW(),HOUR)
Return
SWITCH (
TRUE (),
RELATED ( SalesOrder[Most Recent Delivery Status] ) in { "Staged/Pick
Confirmed"}
&& Hrs>48
, 1,
0
)
No of Days Awaiting Approval = DATEDIFF(Pending_Approvals[Begin Date],TODAY(),DAY)
actual & promised diff =

//var dcd= SELECTEDVALUE(delivery[delivery creation date])
DATEDIFF(SalesOrder[Most Recent Delivery ID Date],SalesOrder[Promise Date],DAY)
Amount USD = CALCULATE(SUM(SalesOrder[Extended Amount USD]))
Amount USD Bucket = 1

/* SWITCH(TRUE(),
ISBLANK(SalesOrder[Amount USD]), Blank() ,
SalesOrder[Amount USD]<=5000 , "$0-$5K",
SalesOrder[Amount USD]>5000 && SalesOrder[Amount USD]<=10000 , "$5K-
$10K",
SalesOrder[Amount USD]>10000 && SalesOrder[Amount USD]<=50000 , "$10K-
$50K",
SalesOrder[Amount USD]>50000 , ">$50K",
BLANK()) */
BackOrder Quantity = //if(SalesOrder[On Backorder]="Yes",SalesOrder[Ordered

Quantity],0)
SWITCH(TRUE(),SalesOrder[Schedule Ship Date]<TODAY() && SalesOrder[Line
Status]<>"AWAITING_SHIPPING", SalesOrder[Ordered Quantity]-SalesOrder[Fulfilled
Quantity],SalesOrder[Line Status]="AWAITING_SHIPPING",SalesOrder[Ordered Quantity]-
[Calc Reservation Quantity in Primary UOM],0)
Calc Ordered Quantity = if(SalesOrder[Line Category Code]="Return",-

1*SalesOrder[Ordered Quantity],SalesOrder[Ordered Quantity])
Calc Reservation Quantity in Primary UOM = CALCULATE(SUM(ReservationDetails[Primary

Reservation Quantity]))
DCodeCountry =
RELATED(SalesOrder_Items[DCode])&"-"&RELATED(SalesOrder_ShipToCustomer[End
Destination Country])
DST_Flag = RELATED(dim_calendar[dst_flag])
Entered Actionable Holds - CS Resp =

var cd= DATEDIFF(SalesOrder[Order Date],NOW(),HOUR)
var hd=IF(
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Customer Service Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Customer Service Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Doctor Certification
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Letter of Credit (H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Pending PO Hold (H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Rental Hold (L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Service Contract Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Awaiting PO Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"EDI GHX Deliverto")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"EDI Quarter End Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Expired Inventory
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG CS Shipping Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"No Reservation Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Pre-Pay Order Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Evaluation Hold (H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Evaluation Hold (L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG International Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"EDI Item Price Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"EDI UOM Hold (L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Manual Pricing Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Manual Pricing Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG ENG STOP ORDER
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG CS Shipping Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Stop Order from
Closing")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"IT Rental Fees")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG EDI REP TRUNK STOCK
HOLD")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"JNJ SPLIT PO")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Rx License Info Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Ground Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Consignment Line
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG CS Distributor
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Close Order Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Invoice Interface
Hold (L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"iStore Quarter End
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Invoice Interface
Hold (H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Consignment Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG EDI Consignment
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"IT Pricing Issue Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Invoice")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"JNJ OPEN ORDER PO")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Saturday Shipping Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Integra Guarantee Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Primatrix Guarantee Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG VAT/TAX
discrepancy")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG High Value Order
Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG IT Invoice Interface
Hold (L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"IT Invoice Interface Hold
(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Manual Stock
Hold(L)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"INTG Finceramica Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Government Order Hold
(H)")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Quarter Shipping Hold")||
CONTAINSSTRING( RELATED(SalesOrder_HoldTypes[Hold Name]),"Return Review Hold
(H)"),0,1)
var holdCnt= CALCULATE(COUNTROWS(DISTINCT(SalesOrder_Holds[Order Line

ID])),Holds_Definition[Hold Name] in {"Manual Allocation Hold (L)","EDI Price Hold
(H)","EDI Duplicate PO Hold (H)","INTG Duplicate PO Hold (H)","Price Override Hold
( H )","Price Override Hold ( L )"})
Return
Switch(True(),holdCnt>0 && hd>0 && SalesOrder[Order Status]="ENTERED" && cd>24,1,0)
/*
var cd= DATEDIFF(SalesOrder[Order Date],NOW(),HOUR)
ID])),Holds_Definition[Hold Name] in {"Manual Allocation Hold (L)","EDI Price Hold
(H)","EDI Duplicate PO Hold (H)","INTG Duplicate PO Hold (H)","Price Override Hold
( H )","Price Override Hold ( L )"})
Return
Switch(True(),holdCnt>0 && SalesOrder[Order Status]="ENTERED" && cd>24,1,0) */
Expected Restocking Fee ($) =

Abs(SalesOrder[Extended Amount USD]) * SalesOrder[Expected Restocking Fee %] //*(-
1)
/* var ER = Calculate(sum(SalesOrder[Amount USD])) * SalesOrder[Expected
Restocking Fee %] *(-1)
Return
CALCULATE((ER),All(SalesOrder[Order Number]) ) */
/* IF(SalesOrder[Line Category Code]="Return",

SWITCH(TRUE(),SalesOrder[RMA Days]<=120 &&
RELATED(SalesOrder_SoldToCustomer[Account Number]) IN
{"85477","88176","202000","427300","427500","427550","427600","427700","703100" },
"15 %" ,
SalesOrder[RMA Days]>120 && SalesOrder[RMA Days] <= 365 &&
// RELATED(SalesOrder_SoldToCustomer[Account Number]) IN
{"85477","88176","202000","427300","427500","427550","427600","427700" }, "50 %" ,
SalesOrder[RMA Days]<=120 && RELATED(SalesOrder_SoldToCustomer[Account
Number]) IN
{"19954","20150","22419","31341","31342","31343","31680","31688","31689","31690","3
1693","31694","31695","31696","31697","31698","31699","31700","31701","31703","3170
5",
"31706","31707","31709","31710","32498","32502","32509","35881","38631","38859","51
562","78074","78210","95407","107375","107376","107378","107380","168400","322900",
"323550","348350","348850","350970","351500","352150","352350","352700","356600","3
60300","360750","360760","361960","540700","626200","631330","631610","631625","656
400"}, "0 %",
SalesOrder[RMA Days]<=90 && RELATED(SalesOrder_SoldToCustomer[Account Number]) IN
{"28795","29398","31346","33088","37112","40001","44665","50922","64340","66298","6
6783","67028","67167","67651","69165","73440","77733","77824","298300","547610"} ,
"0 %" ,
SalesOrder[RMA Days]<=90 &&
CONTAINSSTRING(RELATED(SalesOrder_AutoDiscounts[Modifier]) ,"HPG") ||
CONTAINSSTRING(RELATED(SalesOrder_AutoDiscounts[Modifier]) ,"HCA"),"20 %",
SalesOrder[RMA Days]<=90 &&
CONTAINSSTRING(RELATED(SalesOrder_AutoDiscounts[Modifier]) ,"Vizient"),"10 %" ,
RELATED(SalesOrder_ShipToCustomer[Ship To Region])="North America Domestic" ,"25
%",
RELATED(SalesOrder_ShipToCustomer[Ship To Region])<> "North America Domestic" ,"10
%"
)*/
Expected Restocking Fee % =

var rma = SalesOrder[RMA Days]
var Id1 = SWITCH(TRUE(),
CONTAINSSTRING(RELATED(SalesOrder_AutoDiscounts[Modifier]),"HPG"),"HPG",CONTAINSSTR
ING(RELATED(SalesOrder_AutoDiscounts[Modifier]),"Vizient"),"Vizient")
var Id2 = IF(Related(SalesOrder_ShipToCustomer[Ship To Region])="North America
Domestic","US","NADOM")
var RT1 =
LOOKUPVALUE(Restocking_Fee_Rules[Rule_Type],Restocking_Fee_Rules[Identifier],Id1)
var RT2 =
LOOKUPVALUE(Restocking_Fee_Rules[Identifier],Restocking_Fee_Rules[Identifier],id1)
var RT3 =
var RT4 =
var sd1 = LOOKUPVALUE(Restocking_Fee_Rules[Starting Days From
Shipping],Restocking_Fee_Rules[Identifier],id1)
var RF1 = LOOKUPVALUE(Restocking_Fee_Rules[Restocking Fee
Percent],Restocking_Fee_Rules[Identifier],id1)
var CF = IF( ISBLANK(SalesOrder[RF Rule1 %]) && ISBLANK(ID1) && NOT(RT1) In
{ "GPO","Cust_Number"},1,0)
Return
IF(SalesOrder[Line Category Code]="Return" &&

Related(SalesOrder_TransactionType[Order Type Category]) in {"Credit Card
Returns","Return Order"}, SWITCH(TRUE(),ISBLANK(SalesOrder[RF Rule1 %]) && RT1
="GPO" && RT2 ="HPG" && RMA>=sd1, RF1,
ISBLANK(SalesOrder[RF Rule1 %]) &&
RT1 ="GPO" && RT2 ="Vizient" && RMA>=sd1, RF1,
CF=1 && RT3="Region" && RT4="US"
&& rma >=sd2,RF2,
CF=1 && RT3="Region" &&
RT4="NADOM" && rma >=sd2,RF2,
SalesOrder[RF Rule1 %]))

/*
var rma = SalesOrder[RMA Days]
var acc = RELATED ( SalesOrder_SoldToCustomer[Account Number] )
var Id1 = SWITCH(TRUE(),
CONTAINSSTRING(RELATED(SalesOrder_AutoDiscounts[Modifier]),"HPG"),"HPG",CONTAINSSTR
ING(RELATED(SalesOrder_AutoDiscounts[Modifier]),"Vizient"),"Vizient")
var Id2 = IF(Related(SalesOrder_ShipToCustomer[Ship To Region])="North America
Domestic","US","NADOM")
var RT =
LOOKUPVALUE(Restocking_Fee_Rules[Rule_Type],Restocking_Fee_Rules[Identifier],acc)
var RT2 =
var RT3 =
var RT4 =
var RT5
=LOOKUPVALUE(Restocking_Fee_Rules[Identifier],Restocking_Fee_Rules[Identifier],id2)
Shipping],Restocking_Fee_Rules[Identifier],acc)
var RF1= LOOKUPVALUE(Restocking_Fee_Rules[Restocking Fee
Percent],Restocking_Fee_Rules[Identifier],acc)
Return
IF(SalesOrder[Line Category Code]="Return",

Switch( True(),RT ="Cust_Number"&& rma >=sd1,
CALCULATE((RF1),SalesOrder_SoldToCustomer[Account Number]=acc ) ,
RT2 ="GPO" && RT3 ="HPG" && RMA>=sd2, RF2,
RT2 ="GPO" && RT3 ="vizient" && RMA>=sd2 , RF2,
// RT4="Region" && RT5="US" && (RMA>=sd3 || RT ="Cust_Number" &&
RMA< sd1), RF3,
RT4="Region" && RT5="US" && SWITCH(TRUE(), RMA>=sd3, sd3 ,RT
="Cust_Number" && RMA< sd1, sd1), RF3,
RT4="Region" && RT5="NADOM" && RMA>=sd3, RF3
)) */
Extended Amount = SalesOrder[Calc Ordered Quantity]*

SalesOrder[Unit Selling Price]
Extended Amount USD = SalesOrder[Calc Ordered Quantity]*

SalesOrder[Unit Selling Price]*
(if(SalesOrder[Currency]="USD",1,RELATED('Fx
Rates'[Conversion Rate])))
Follow Up Notes =
CALCULATE(MAX(SalesOrder_Followup[SHORT_TEXT]),SalesOrder_Followup[Most Recent
Comment]=1)
Future Date =
var rd = SalesOrder[Request Date]
var td = TODAY()
var ssd = SalesOrder[Schedule Ship Date]
var rslt = if (OR(rd>td,ssd>td),"Yes","No")
RETURN
rslt
Future Date Num = IF(SalesOrder[Future Date]="Yes",1,0)
International_Flag = SWITCH(TRUE(),
RELATED(SalesOrder_ShipToCustomer[ShipTo_Flag])=RELATED(SalesOrder_Warehouse[ShipFr
om_Flag]),"No","Yes")
Item Orderability Flag =

var cnt =
CALCULATE(COUNTROWS(Item_Orderability),filter(Item_Orderability,Item_Orderability[C
ountry Code]=SalesOrder[Orderability Country] && Item_Orderability[Inventory Item
ID]=SalesOrder[Inventory Item ID]))
Return
IF(Cnt>0, "N","Y"
Manual Allocations Hold-EMEA =

/*VAR cnt =
CALCULATE (
COUNTROWS ( DISTINCT ( SalesOrder_Holds[Order Line ID] ) ),
Holds_Definition[Hold Name] In {"Manual Allocation Hold (L)"} )
VAR Reserv = Related(Onhand[Reserved])
// Var BackOrderQty = SalesOrder[Ordered Quantity]-ReservQty// If(SalesOrder[On

Backorder]="Yes",SalesOrder[Ordered Quantity],0)
Return
Switch(True(),Cnt>=1 && SalesOrder[Line Status]="AWAITING_SHIPPING" &&
ReservQty<=0 ,1,0) */
VAR cnt =
CALCULATE (
COUNTROWS ( DISTINCT ( SalesOrder_HoldTypes[Order Line ID] ) ),
CONTAINSSTRING(SalesOrder_HoldTypes[Hold Name],"Manual Allocation Hold
(L)") && NOT(CONTAINSSTRING(SalesOrder_HoldTypes[Hold Name],"Credit Check
Failure")),ALLEXCEPT(SalesOrder,SalesOrder[Line ID]))
var reservableOnhand = RELATED(Onhand[Reservable Onhand])
Return
Switch(True(), cnt>=1 && SalesOrder[Line Status]="AWAITING_SHIPPING" &&
ISBLANK(SalesOrder[Reserved Quantity]) && SalesOrder[BackOrder Quantity]>=1 &&
reservableOnhand>=1 ,1,0)
Most OLD Delivery ID Date = IF(NOT(ISBLANK(SalesOrder[Most Recent Delivery ID])),

CALCULATE(MIN(Delivery[Delivery Creation
Date]),
FILTER(ALL(Delivery),Delivery[Delivery
ID]=EARLIEST(SalesOrder[Most Recent Delivery ID])))
)
Most Recent Delivery ID = CALCULATE(MAX(Delivery[Delivery ID]))
Most Recent Delivery ID Date = IF(NOT(ISBLANK(SalesOrder[Most Recent Delivery

ID])),
CALCULATE(MAX(Delivery[Delivery Creation
Date]),
FILTER(ALL(Delivery),Delivery[Delivery
ID]=EARLIER(SalesOrder[Most Recent Delivery ID])))
)
Most Recent Delivery Status = CALCULATE(MAX('Delivery'[Released Status]),

FILTER(ALL(Delivery),Delivery[Order Line
ID]=EARLIER(SalesOrder[Line ID]))
)
Most Recent Hold Comment =

var rslt =
CALCULATE(MIN(SalesOrder_HoldComments[SHORT_TEXT]),SalesOrder_HoldComments[Most
Recent Comment]=1)
RETURN
//LEFT(rslt,50)
rslt
Not On Backorder Or On Hold = IF(SalesOrder[On Backorder]="No" || SalesOrder[On

Hold]="No",1,0)
On Backorder =
VAR bo = CALCULATE(COUNT(Delivery[Delivery Detail ID]),Delivery[Released Status
Code]="B")
bo>0,"Yes",
"No")
RETURN
rslt
On Backorder due to RADB =

var reservableQty = CALCULATE(SUM(Onhand[Reservable Onhand]))
RETURN
IF(SalesOrder[On Backorder]="Yes" &&
RELATED(SalesOrder_Items[RADB Enabled])="Yes" &&
SalesOrder[Ordered Quantity]<=reservableQty,"Yes",
"No")
On Backorder Num = IF(SalesOrder[On Backorder]="Yes",1,0)
On Hold = if(CALCULATE(COUNT(SalesOrder_Holds[Order Hold ID]))>0,

"Yes","No")
On Hold Num = IF(SalesOrder[On Hold]="Yes",1,0)
On Hold Released = if(CALCULATE(COUNT(SalesOrder_Holds_Released[Order Hold ID]))>0,

"Yes","No")
Order Date (Local) =

VAR orderDate =
SELECTEDVALUE ( SalesOrder[Order Date] )
VAR usr = LEFT(USERPRINCIPALNAME (),SEARCH("@",USERPRINCIPALNAME ())-1)
/* LEFT (
USERPRINCIPALNAME (),
LEN ( USERPRINCIPALNAME () ) - SEARCH ( "@", USERPRINCIPALNAME () ) ) */
/* LEFT (
USERNAME(),
LEN (USERNAME() ) - SEARCH ( "@", USERNAME() )) */
// "TOSHIO.AMO" //
VAR TZ_DST0 =
LOOKUPVALUE(
DIM_R12_User_Timezone[EST_Offset_DST],
DIM_R12_User_Timezone[User_Name], usr
)
VAR TZ_DST1 =
ABS(LOOKUPVALUE (
))
VAR TZ_NoDST0 =
LOOKUPVALUE (
DIM_R12_User_Timezone[EST_Offset_NoDST],
)
VAR TZ_NoDST1 =
ABS(LOOKUPVALUE (
))
VAR UN =
LOOKUPVALUE (
DIM_R12_User_Timezone[User_Name],
)
VAR DST =
SELECTEDVALUE ( SalesOrder[DST Flag] ) // Var FormatStr = "mm/dd/yyyy hh:mm:ss"
//var Od = format(HASONEVALUE(SalesOrder[Order Date]),FormatStr)
//var OD_DST_Y = IF(DST="Y",Od+TZ_DST,Od+TZ_NoDST)
VAR OD =
SWITCH (
TRUE (),
ISBLANK(TZ_DST0),orderDate,
DST = "Y", IF(TZ_DST0 <0, orderDate - TIME ( TZ_DST1, 0,
0 ),orderDate+TIME(TZ_DST0,0,0)),
DST = "N", IF(TZ_NoDST0 <0 , orderDate - TIME ( TZ_NoDST1, 0, 0 ),
orderDate + TIME(TZ_NoDST0,0,0))
)
RETURN
/*IF( usr=UN,
OD,orderDate) */
OD
// logic before the above updated

/* VAR orderDate =
SELECTEDVALUE ( SalesOrder[Order Date] )
VAR usr = LEFT(USERPRINCIPALNAME (),SEARCH("@",USERPRINCIPALNAME ())-1)
VAR TZ_DST =
LOOKUPVALUE (
)
VAR TZ_NoDST =
LOOKUPVALUE (
)
VAR UN =
LOOKUPVALUE (
)
VAR DST =
SELECTEDVALUE ( SalesOrder[DST Flag] ) // Var FormatStr = "mm/dd/yyyy hh:mm:ss"
VAR OD =
SWITCH (
TRUE (),
ISBLANK(TZ_DST),orderDate,
DST = "Y", orderDate + TIME ( TZ_DST, 0, 0 ),
DST = "N", orderDate + TIME ( TZ_NoDST, 0, 0 )
)
RETURN
OD
*/
Order Days = DATEDIFF(SalesOrder[Order Date],TODAY(),DAY)
Order Type Group =

IF(SalesOrder[Line Category Code]="R","Return",
IF(SEARCH("STANDARD", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Standard Order",
IF(SEARCH("REPAIR", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Repair Order",
IF(SEARCH("INTERNAL", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Internal Order",
IF(SEARCH("INTERCOMPANY", RELATED(SalesOrder_TransactionType[Order Type]),1,0)
<> 0,"Intercompany Order",
IF(SEARCH("BILL ONLY", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Bill Only Order",
IF(SEARCH("EVAL", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Evaluation Order",
IF(SEARCH("SAMPLE", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Sample Order",
IF(SEARCH("RENTAL", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Rental Order",
IF(SEARCH("PRODUCT REQUEST", RELATED(SalesOrder_TransactionType[Order
Type]),1,0) <> 0,"Product Request Order",
IF(SEARCH("DONATION", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Charity Donation Order",
IF(SEARCH("CHARGE", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"Charge Sheet Order",
IF(SEARCH("EDC SET", RELATED(SalesOrder_TransactionType[Order Type]),1,0) <>
0,"EDC Set Order",
RELATED(SalesOrder_TransactionType[Order Type])
)) )))))))))))
Orderability Country =
SWITCH (
TRUE (),
// NOT ( ISBLANK ( SalesOrder[Destination Country] ) ), SalesOrder[Destination
Country],
NOT ( ISBLANK ( RELATED ( SalesOrder_DeliverToCustomer[Deliver To
Country] ) ) ), RELATED ( SalesOrder_DeliverToCustomer[Deliver To Country] ),
RELATED ( SalesOrder_ShipToCustomer[Ship To Country] )
)
Out For Quarter = IF(ISBLANK(SalesOrder[Out For Quarter Comment]),"No","Yes")
Out For Quarter Comment =

var rslt =
CALCULATE(MIN(SalesOrder_OutForQuarter[SHORT_TEXT]),SalesOrder_OutForQuarter[Most
Recent Comment]=1)
RETURN
rslt
Out for Quarter Num = IF(SalesOrder[Out For Quarter]="Yes",1,0)
Price Holds =
ID])),Holds_Definition[Hold Name] in {"EDI Item Price Hold (L)","EDI Price Hold
(H)","Manual Pricing Hold (H)","Manual Pricing Hold (L)"})
Return
Switch(True(),holdCnt>0 && RELATED(SalesOrder_OperatingUnit[Operating Unit])="OU
United States" && Related(SalesOrder_ShipToCustomer[Ship To Region])="North America
Domestic" && RELATED(SalesOrder_TransactionType[Order Type])="ILS Standard
Order",1,0)
RADB Country =
var destinationCountry = SalesOrder[Destination Country]
var deliverCountry = RELATED(SalesOrder_DeliverToCustomer[Deliver To Country])
var shipCountry = RELATED(SalesOrder_ShipToCustomer[Ship To Country])
return
COALESCE(destinationCountry,deliverCountry,shipCountry)
RestockingFee = RELATED(OpenOrder_Restocking_Fee[Restocking Fee])
RestockingFee_Discrepancy =
var ExpectedRestockingFee =CALCULATE( SUM(SalesOrder[Expected Restocking Fee ($)]))
//var ActualRestockingFre = CALCULATE(sum(OpenOrder_Restocking_Fee[Restocking
Fee]),
FILTER(OpenOrder_Restocking_Fee,OpenOrder_Restocking_Fee[Header_ID]=VALUES(SalesOrd
er[Header ID])))
Var ActualRestockingFre= SELECTEDVALUE(SalesOrder[RestockingFee])
Return
//ActualRestockingFre
--SELECTEDVALUE(SalesOrder[RestockingFee])
IF( (ExpectedRestockingFee-ActualRestockingFre >2) || (ActualRestockingFre-
ExpectedRestockingFee)>2,"Y", "N")
//IF( Round(SalesOrder[Expected Restocking Fee ($)],0)=

Round(RELATED(OpenOrder_Restocking_Fee[Restocking Fee]),0),"Y", "N")
// SalesOrder[Expected Restocking Fee ($)]<

RELATED(OpenOrder_Restocking_Fee[Restocking Fee]),1) -- Yellow
RestockingFee_Discrepancy_1 = IF( Round(SalesOrder[Expected Restocking Fee ($)],0)=

Round(RELATED(OpenOrder_Restocking_Fee[Restocking Fee]),0),"Y", "N")
// SalesOrder[Expected Restocking Fee ($)]<

RELATED(OpenOrder_Restocking_Fee[Restocking Fee]),1) -- Yellow
RF Rule1 % = IF(SalesOrder[Line Category Code]="Return" &&

Related(SalesOrder_TransactionType[Order Type Category]) in {"Credit Card
Returns","Return Order"},SWITCH(TRUE(), SalesOrder[RMA
Days]>=related(Restocking_Fee_Rules[Starting Days From
Shipping]),RELATED(Restocking_Fee_Rules[Restocking Fee Percent])))
RMA Days = // DATEDIFF(RELATED(OpenReturnOrder_Detail[Orig Shipment

Date]),SalesOrder[Order Date],DAY)
IF(SalesOrder[Line Category
Code]="RETURN",DATEDIFF(RELATED(OpenReturnOrder_Detail[Orig Shipment
Date]),SalesOrder[Line Entered Date],DAY))
RMA Days Bucket =

SWITCH(TRUE(),
ISBLANK(SalesOrder[RMA Days Old]), "D. RMA Days Blank" ,
SalesOrder[RMA Days Old]>90 , "C. > 90 Days",
SalesOrder[RMA Days Old]>30 , "B. 31-90 Days",
SalesOrder[RMA Days Old]>=0 , "A. 0-30 Days",
"D. RMA Days Blank")
RMA Days Old = //IF(SalesOrder[Line Category

Date]),SalesOrder[Request Date],DAY))
IF(SalesOrder[Line Category
Date]),SalesOrder[Line Entered Date],DAY))
Rows in Backorder =
VAR cnt = CALCULATE(DISTINCTCOUNT(Delivery[Order Line ID]),
Delivery[Released Status Code]="B")
VAR rslt = IF(ISBLANK(cnt),0,cnt)
RETURN
rslt
Rows in Delivery Details =

VAR cnt = CALCULATE(DISTINCTCOUNT(Delivery[Delivery Detail ID]))
RETURN
rslt
Rows in Other Status =
NOT(Delivery[Released Status Code] IN {"R","Y","S","B"}))
RETURN
rslt
Rows Ready To Release =

Delivery[Released Status Code]="R")
RETURN
rslt
Rows Released to Warehouse =

Delivery[Released Status Code]="S")
RETURN
rslt
Rows Staged/Pick Confirmed =

Delivery[Released Status Code]="Y")
RETURN
rslt
TZ_DST =
LOOKUPVALUE (
DIM_R12_User_Timezone[User_Name], [UserName]
)
UN =
LOOKUPVALUE (
DIM_R12_User_Timezone[User_Name], [UserName] )
UserName = LEFT (
USERPRINCIPALNAME(),
LEN ( USERPRINCIPALNAME()) - SEARCH ( "@", USERPRINCIPALNAME() ))
UserName_Prin =
var x= USERPRINCIPALNAME()
Return
LEFT(x,SEARCH("@",x)-1)
//LEFT ( USERPRINCIPALNAME(), LEN ( USERPRINCIPALNAME() ) - SEARCH ( "@",

USERPRINCIPALNAME() ) )
//LEFT (x, LEN ( x )-y )

/*LEFT (RIGHT ( USERNAME (), LEN ( USERNAME () ) - SEARCH ( "\", USERNAME () ) )
x,LEN (x ) - SEARCH ( "@", x )) */
/*LEFT (
USERPRINCIPALNAME(),
LEN (USERPRINCIPALNAME() ) - SEARCH ( "@", USERPRINCIPALNAME() )) */
UTC_OrderDate = If(SalesOrder[DST_Flag]="Y",SalesOrder[Order Date]-
TIME(4,0,0),SalesOrder[Order Date]-TIME(5,0,0))
Auth Code Len = LEN(SalesOrder_CreditCard_Auth[Auth Code])
No Line or Customer CS Resp =

SWITCH (
TRUE (),
[Order Header Status] = "Entered"
&& [Transaction Type]
IN {
"CA Return Order",
"CA Sample Order",
"CH Demo Order",
"ILS Return Order",
"JP Sample Order",
"UK EDC Set Shipment",
"NL Standard Order", "BE Standard Order"
}, 1,
0
)
No Line or Customer Not CS Resp =

SWITCH (
TRUE (),
[Order Header Status] = "Entered"
&& [Transaction Type]
IN {
"AU PM Order",
"AU Repair Order",
"CA PM Order",
"CA Repair Order",
"CH Repair Order",
"ILS Eval Order",
"ILS Repair Order",
"ILS Sample Order",
"NZ Repair Order",
"YRK Repair Order"
}, 1,
0
)
Amount on Hold USD = CALCULATE(SUM(SalesOrder[Extended Amount USD]))
Days on Hold = DATEDIFF(SalesOrder_Holds[Hold Date],TODAY(),DAY)
Hold Aging =
VAR d = SalesOrder_Holds[Days on Hold]
d>90 , "90+ Days",
d>=61, "61-90 Days",
d>=31, "31-60 Days",
d>=11, " 11-30 Days",
d>=6, " 06-10 Days",
d>=3, " 03-05 Days",
d>=0, " 00-02 Days")
RETURN
rslt
Released Days. = DATEDIFF(SalesOrder_Holds_Released[Hold

Date],SalesOrder_Holds_Released[Hold Released Date],DAY)
DFF ECCN Flag = SWITCH(TRUE(),RELATED(SalesOrder_Warehouse[ShipFrom_Flag]) in

{"US","PR"},"Yes", "NA")
DFF Schedule B Flag = SWITCH(TRUE(),RELATED(SalesOrder_Warehouse[ShipFrom_Flag]) in

{"US","PR"},"Yes","NA")
EU Flag =
SWITCH (
TRUE (),
RELATED ( SalesOrder_Warehouse[ShipFrom_Flag] )
IN { "EU" }
&& (
RELATED ( SalesOrder_Items[DFF ECCN 135] ) = " "
|| RELATED ( SalesOrder_Items[DFF EU Commodity Code] ) = " "
), "Yes",
"No"
)
SW Flag =
SWITCH (
TRUE (),
IN { "SW" }
&& (
RELATED ( SalesOrder_Items[EU Commodity Code in 191] ) = " "
|| RELATED ( SalesOrder_Items[DFF ECCN 191] ) = " "
), "Yes",
"No"
)
UK Flag =
SWITCH (
TRUE (),
IN { "UK" }
&& (
RELATED ( SalesOrder_Items[EU Commodity Code in 415] ) = " "
|| RELATED ( SalesOrder_Items[DFF ECCN 415] ) = " "
), "Yes",
"No"
)
USA Or PR Flag =
SWITCH (
TRUE (),
IN { "US", "PR" }
&& (
RELATED ( SalesOrder_Items[DFF Schedule B] ) = " "
|| RELATED ( SalesOrder_Items[DFF ECCN] ) = " "
|| RELATED ( SalesOrder_Items[DFF License Required] ) = ""
), "Yes",
"No"
)
Division =
//VAR dvsn = IF(SalesOrder_Items[DCode]="DEFLT",SalesOrder_Items[Division Item
Level],RELATED(DCodeGlobalProductRollup[Rollup Level1]))
VAR dvsn = SalesOrder_Items[Division Item Level]
dvsn in {"RECON","OTT"},"OTT",
dvsn IN {"SSS","Codman","NEURO","INSTR"},"CSS",
"Others")
RETURN
rslt
Is Non-Returnable SKU = //LOOKUPVALUE('CS Non Returnable SKU Master'[SKU

Number],'CS Non Returnable SKU Master'[SKU Number],SalesOrder_Items[Item Number])
var x= RELATED('CS Non Returnable SKU Master'[SKU Number])
Return
IF(SalesOrder_Items[Item Number]=x,1,0)
Non-Returnable SKU = //LOOKUPVALUE('CS Non Returnable SKU Master'[SKU Number],'CS

Non Returnable SKU Master'[SKU Number],SalesOrder_Items[Item Number])
Return
IF(SalesOrder_Items[Item Number]=x,"Y","N")
Returnable Flag = //LOOKUPVALUE('CS Non Returnable SKU Master'[SKU Number],'CS Non
Returnable SKU Master'[SKU Number],SalesOrder_Items[Item Number])
Return
//IF(ISBLANK(SalesOrder_Items[Item Number]),"Y","N")
IF(SalesOrder_Items[Item Number]=x,"N","Y")
Returnable Flag Num = //LOOKUPVALUE('CS Non Returnable SKU Master'[SKU Number],'CS

Non Returnable SKU Master'[SKU Number],SalesOrder_Items[Item Number])
Return
IF(SalesOrder_Items[Item Number]=x,0,1)
Int'l OU Group =
SWITCH(TRUE(),
SalesOrder_OperatingUnit[Operating Unit] IN {"France Sales OU",
"Germany Sales OU",
"Swiss Sales OU",
"Netherlands Sales OU",
"OU Belgium",
"OU Italy",
"OU UK",
"OU Ireland"}, "EU OTT"
)
US_OUS = IF(SalesOrder_OperatingUnit[Operating Unit]="OU United

States","US","International")
ShipTo_Flag = SWITCH(TRUE(),SalesOrder_ShipToCustomer[Ship To Country] IN

{"AT","BE","BG","HR","CY","CZ","DK","EE","FI","FR","DE","GR","HU","IE","IT","LV","L
T","LU","MT","NL","PL","PT","RO","SK","SI","ES","SE"},"EU",SalesOrder_ShipToCustome
r[Ship To Country])
//,SalesOrder_ShipToCustomer[Ship To Country] IN {"US","PR"},"US" -- removed this
as per update in the logic from Priti
Excluded Order Type Category = IF(ISBLANK(RELATED('Excluded Order Type

Category'[Order Type Category])),"No","Yes")
ShipFrom_Flag = SWITCH(TRUE(),SalesOrder_Warehouse[Country] IN
{"AT","BE","BG","HR","CY","CZ","DK","EE","FI","FR","DE","GR","HU","IE","IT","LV","L
T","LU","MT","NL","PL","PT","RO","SK","SI","ES","SE"},"EU",SalesOrder_Warehouse[Cou
ntry])
// ,SalesOrder_Warehouse[Country] IN {"US","PR"} -- Removed this as per update in
update in logic
MTD = CALCULATE(SUM('Trial Balance Activity'[Period Activity]),DATESMTD('Trial

Balance Activity'[End Date]))
MTD SPLY = CALCULATE(SUM('Trial Balance Activity'[Period

Activity]),SAMEPERIODLASTYEAR(DATESMTD('Trial Balance Activity'[End Date])))
QTD = CALCULATE(SUM('Trial Balance Activity'[Period Activity]),DATESQTD('Trial
QTD SPLY = CALCULATE(SUM('Trial Balance Activity'[Period

Activity]),SAMEPERIODLASTYEAR(DATESQTD('Trial Balance Activity'[End Date])))
YTD = CALCULATE(SUM('Trial Balance Activity'[Period Activity]),DATESYTD('Trial

YTD SPLY = CALCULATE(SUM('Trial Balance Activity'[Period

Activity]),SAMEPERIODLASTYEAR(DATESYTD('Trial Balance Activity'[End Date])))
Measure =
VAR _select =
SELECTEDVALUE ( Slicer[Name] )
RETURN
IF (
_select = "Estimate Name",
CALCULATE (
MAX ( Append1[Value] ),
FILTER ( Append1, Append1[Column] <> "Hidden Name" )
),
CALCULATE (
MAX ( Append1[Value] ),
FILTER ( Append1, Append1[Column] <> "Estimate Name" )
)
)
******************************************************
Measures created as shown:
Earliest Receipt Date = CALCULATE(MIN('Material Transactions'[Transaction Date]),
'Material Transactions'[Transaction_Type_ID]=18)
Latest Ship Date = CALCULATE(MAX('Material Transactions'[Transaction
Date]),'Material Transactions'[Transaction_Type_ID]=62)
Recipt to Ship Cycle Time (Days) = DATEDIFF([Earliest Receipt Date],[Latest Ship
Date],DAY)
Measeure did not work - performance issue
Created calculated columns:
performance increased. but output is varchar

Calculated column:
Earliest Receipt Datecc =
VAR item_number = RELATED('Item Basic'[Item Number])
var item_description =RELATED('Item Basic'[Item Description])
VAR lot_number = 'Material Transactions'[Lot Number]
RETURN
CALCULATE(
Min('Material Transactions'[Transaction Date]),
ALL('Material Transactions'),
'Material Transactions'[Transaction_Type_ID]=18,
'Item Basic'[Item Number] = item_number,
'Item Basic'[Item Description] = item_description,
'Material Transactions'[Lot Number] = lot_number)
created a summarised table

405 End to End Cycle =
var po = SUMMARIZE(
FILTER('Material Transactions','Material
Transactions'[Organization_ID]=2923 && 'Material Transactions'[Transaction_Type_ID]
=18),
'Material Transactions'[Organization_ID],'Material
Transactions'[Inventory_Item_ID],'Material Transactions'[Lot Number],
"PO Receipt Date",MIN('Material Transactions'[Transaction Date]))
var ship = SUMMARIZE(

FILTER('Material Transactions','Material
Transactions'[Organization_ID]=2923 && 'Material Transactions'[Transaction_Type_ID]
=62),
'Material Transactions'[Organization_ID],'Material
Transactions'[Inventory_Item_ID],'Material Transactions'[Lot Number],
"Ship Date",MAX('Material Transactions'[Transaction Date]))
var rslt = NATURALLEFTOUTERJOIN(ship,po)
RETURN
rslt
-- Anasco issue
Technicians =
var selectedOrg =SELECTEDVALUE('Preventive WO'[org])
var techn165 = SELECTEDVALUE('Preventive WO'[DFF Technician Name])

var techn = VALUES(Operations[User Name])
RETURN
if (selectedOrg="165",techn165,techn)
Org = RELATED(Activity[org])
Manufacturing Hold Flag = if(CALCULATE(DISTINCTCOUNT(ManualAllocationHold[Inventory

Item ID]))=BLANK(),"NO","YES")
Manufacturing Hold = CALCULATE(DISTINCTCOUNT(ManualAllocationHold[Inventory Item

ID]))
***********************************************************************************
***********
Raw Material QTY calculation in BOM resource report
Raw Mat QTY =
var DE1 =IF(ISBLANK('BOM Oracle'[DE.1]),1,'BOM Oracle'[DE.1])
return DE1 * DE2 * DE3 * DE4 * DE5 * DE6
choose supplier name using if
Supplier Name =
// VAR org_item = 'BOM Oracle'[COrgID-ItemID]
VAR from_sourcing = FIRSTNONBLANK('Sorcing Rule'[Vendor_Name], TRUE())
VAR from_quotation = CALCULATE( FIRSTNONBLANK(PO[Vendor_Name], TRUE()),
FILTER(PO, PO[Order Type]="QUOTATION") )
VAR from_bpa = CALCULATE( FIRSTNONBLANK(PO[Vendor_Name], TRUE()),
FILTER(PO, PO[Order Type]="BLANKET") )
VAR from_asl = FIRSTNONBLANK(ASL[Vendor_Name], TRUE())
VAR from_standard = CALCULATE( FIRSTNONBLANK(PO[Vendor_Name], TRUE()),
FILTER(PO, PO[Order Type]="STANDARD") )
// RETURN from_sourcing
RETURN IF(NOT ISBLANK(from_sourcing), "sourcing " & from_sourcing,

IF(NOT ISBLANK(from_quotation), "quotation " & from_quotation,
IF(NOT ISBLANK(from_bpa), "bpa " & from_bpa,
IF(NOT ISBLANK(from_asl), "asl " & from_asl,
IF(NOT ISBLANK(from_standard), "po " & from_standard,
"Sorry No Supplier"
)))))
***********************
Break QTY =
CALCULATE(
FIRSTNONBLANK(PO[Break Quantity], TRUE()),
FILTER(
PO, OR(
PO[Order Type] = "BLANKET",
PO[Order Type] = "QUOTATION"))
)
Period_EndDate_LYr =
var period_endLYr=ENDOFMONTH(DATEADD('Period Not Linked'[End_Date],-1,YEAR))
var x= IF(NOT(ISBLANK(period_endLYr)),period_endLYr,0)
return x
MTD Last Year Same Period =
VAR period_name_TYr = 'PL BS Trend Primary Imported'[Period Name]
VAR period_start_LYr = LOOKUPVALUE('Period Not
Linked'[Period_StartDate_LYr], 'Period Not Linked'[Period_Name], period_name_TYr)
VAR period_end_LYr = LOOKUPVALUE('Period Not Linked'[Period_EndDate_LYr],
'Period Not Linked'[Period_Name], period_name_TYr)
VAR mtd_total_LYr = CALCULATE(
SUM('PL BS Trend Primary Imported'[Period Activity]),
'GL Period'[Start_Date]>=period_start_LYr && 'GL
Period'[End_Date]<=period_end_LYr,ALLEXCEPT('PL BS Trend Primary Imported','PL BS
Trend Primary Imported'[Period Activity]))
RETURN mtd_total_LYr //period_start_LYr & " To " & period_end_LYr
****************************
1 to many , to 1 combination problem
Invoice Amount =
CALCULATE( SUM(FieldService_Invoices[Invoice Line Amount]),
ALLEXCEPT(FieldService_Invoices,
FieldService_Invoices[Order Number]))

DAX Expressions new

Uploaded by

Copyright:

Available Formats

DAX Expressions new

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DAX Expressions new

Uploaded by

Copyright:

Available Formats

sql-movemedical-prod-useast-01.database.windows.

EU COMMODITY CODE Discrepancy =

ECCN in Org 135 = CALCULATE(MAX(Item_OrgInfo[Org

510k/PMA/Exempt Discrepancy = Switch(True(),

COO Discrepancy = Switch(True(),

Org-Assembly Item ID = Bill_of_Materials[Organization ID] & "-" &

Invoice Aging Bucket =

ECCN Discrepancy = Switch(True(),

IF( NOT ( Item_Orginfo[Organization ID] IN

ECCN Discrepancy = Switch(True(),

Org-Assembly Item ID = Bill_of_Materials[Organization ID] & "-" &

EU ECCN in Org 415 = CALCULATE(MAX(Item_OrgInfo[Org

Item Status MST = CALCULATE(MAX(Item_OrgInfo[Item

Hold Owner = SWITCH(TRUE(),

Discount Lost & Taken = IF('Invoice Payments'[Discount Taken]=0 && 'Invoice

Invoice Aging Bucket = IF('Invoice Payments'[Invoice payments Aging] <=0 ,"Within

EU Commodity Code in 191 = CALCULATE(MAX(Item_OrgInfo[DFF EU Commodity

var b = if(a<=1000," 0-1000",

\\(c IN {"191","415"} ) && (a<>b),"NO",

Invoice Variance FLAG = IF([Invoice Count Difference]=0,"NO","YES")

% ≥30 Days holds = DIVIDE([≥30 Days holds],Count('Invoice Holds'[Hold Id]),0)

Total Invoices With Received Dates = CALCULATE(count('Invoice Header'[Invoice

Discvount Lost & Taken = IF('Invoice Payments'[Discount Taken]=0 && 'Invoice

Invoice Aging Bucket = IF('Invoice Payments'[Invoice payments Aging] <=0 ,"Within

Invoice payments Aging = DATEDIFF('Invoice Payments'[Due Date],'Invoice

Total Discount = 'Invoice Payments'[Discount Lost]+'Invoice Payments'[Discount

Vendor Num Name = CONCATENATE(CONCATENATE(VendorSite_PO[Vendor

Vendor Num Name = CONCATENATE(CONCATENATE(VendorSite_PO[Vendor

Inspection Days = DATEDIFF(Receipts[Receipt Date],Receipts[Inspected Date],DAY)

Balance Amount USD = PurchaseOrder[Amount USD]-PurchaseOrder[Amount Billed USD]

Quantity Remaining = PurchaseOrder[Quantity Ordered]-PurchaseOrder[Quantity

Rejected Amount USD = PurchaseOrder[Quantity Rejected]*PurchaseOrder[Unit

Remaining Open PO Balance USD = IF(PurchaseOrder[Line Closed Code] in

Remaining PO Balance = (PurchaseOrder[Unit Price]*PurchaseOrder[Quantity

var b = if(a<=1000," 0-1000",if(a<=5000," 1001-5000",if(a<=25000," 5001-

Count of Holding days =

Hold Owner = SWITCH(TRUE(),'Invoice Holds'[Hold Name] in {"PO NOT

Measure = calculate(average('Invoice Holds'[Due Days]),filter('Invoice

Discount Lost & Taken = IF('Invoice Payments'[Discount Taken]=0 && 'Invoice

Invoice Aging Bucket = IF('Invoice Payments'[Invoice payments Aging] <=0 ,"Within

Invoice payments Aging = DATEDIFF('Invoice Payments'[Due Date],'Invoice

Total Discount = 'Invoice Payments'[Discount Lost]+'Invoice Payments'[Discount

Invoice Hold Flag = IF(ISBLANK('Invoice Header'[Hold Id]),"NO","YES")

Payment Term Flag = IF('Invoice Distribution'[PO Payment Term]='Invoice

PO Payment Term = RELATED(PurchaseOrder[Payment Term])

Amount Limit in USD =

Product Division = RELATED(DCode_GlobalProductRollup[Product Division Code])

Blizzard Modifier Flag =

BillTo matches EndDest = IF('Bill To Country'[Bill To Country]='Bill To

End Destination Country =

PaymentMethod & TypeFlag = CONCATENATE(Payments[Payment Type

Org-Assembly Item ID = Bill_of_Materials[Organization ID] & "-" &

Delay Jobs % = DIVIDE('_Measures'[Count of Delay WO], '_Measures'[Count of

Count of WO = '_Measures'[Count of Preventive WO] + '_Measures'[Count of Corrective

Count of Preventive WO = DISTINCTCOUNT('Preventive_WO'[Work Order Number])

Count of Delay WO = CALCULATE(COUNT('Preventive_WO'[Work Order

Count of Corrective WO = DISTINCTCOUNT('Corrective WO'[Work Order Number])

Corrective Jobs % = DIVIDE('_Measures'[Count of Corrective WO],'_Measures'[Count of

Inspection Due = Switch(True(),

Component Consumption Difference % =