Hi All. Thanks in advance for any help cracking this nut.
I have four tables:
fRepair(OrderNum, OrderDate, CompleteDate)
fRepairWIP(OrderNum, WorkCenter, WCStart, WCComplete)
fBadWIP(WorkCenter)
dimDate(Day, Month, etc)
My goal is to calculate a running total of the Repair Turnaround Time (TAT) for valid Work Centers (i.e. non Bad WIP) on Open Orders. I currently have fRepair.OrderDate actively linked to dimDate[Day] and a passive link btw fRepair[CompleteDate] and dimDate[Day]. I'm trying to avoid create a 'snowflake' loop between fRepairWIP --> dimDate --> fRepair to avoid issues with user applied filters in the susequent reports.
My calculation so far:
AvgOpenDate:=
CALCULATE(
AVERAGEX(
FILTER(fRepair,
OR(fRepair[CompleteDate]>MAX(dimDate[Day]), ISBLANK(fRepair[CompleteDate]))),
fRepair[OrderDate])
, FILTER(ALL(dimDate), dimDate[Day]<=MAX(dimDate[Day]))
)
I then calculate TAT as:
OpenTAT:=
CALCULATE(
MAX(dimDate[Day])-[AvgOpenDate],
FILTER(dimDate, dimDate[Day]<=TODAY())
)
This gives me the running full TAT for Open Orders (where fRepair[CompleteDate] is in the 'future' or fRepair[CompleteDate] is blank)
My next challenge is to calculate the running total of TAT on Open Orders less the amount of time spent in 'BAD' work centers. I can add SUM(fBadWIP) into the fRepairWIP table through SQL queries back to the primary database, but either way I need to correlate that into the TAT calculation by comparing the fRepairWIP[WCStart] to the running date and subtracting that difference from the running Open order TAT calculation.
Something like this comes to mind, but I have not been successful yet:
AvgAdjustment:=
CALCULATE(
AVERAGEX(FILTER(fRepairWIP, fRepairWIP[WCStart]>=MAX(dimDate[Day] &&
fRepairWIP[WCComplete]>MAX(dimDate[Day]),
MAX(dimDate[Day])-fRepairWIP[WCStart])
, FILTER(ALL(dimDate), dimDate[Day]<=MAX(dimDate[Day]))
)
OpenTATAdjusted:=
CALCULATE(
MAX(dimDate[Day])-[AvgOpenDate] - [AvgAdjustment],
FILTER(dimDate, dimDate[Day]<=TODAY())
)
Performance is somewhat of a concern since fRepair can be 20k+ entries and fRepairWIP can be 50k+ entries or more, so any suggestions on streamlining the calculations is also helpful. It would be nice to dump the secondary SQL query and do all the calculations in DAX somehow, but I have a hard enough keeping the calculations straight as is...
Hopefully I have explained this closely enough for some of the power users out there, but please ask for clarification if the explanation is a bit muddled. I have a hard enough time describing it to even search the forum entries very fluently.
Thanks.
EDIT - I realized I cannot calculate AVERAGEX against the fRepairWIP table since that would apply an average across the WIP entries and not the average across Open Orders as intended. To remedy, my latest thinking is to SUMX across the fRepairWIP table and then divide by a COUNTROWS against the Open Orders in fRepair over time. There has to be a better way...
