Forum Discussion
Leadtime evaluation impossible with this scenario?
Hi,
I have a problem with a leadtime evaluation that I would like help with:
My datasource is DB2 and I use import mode.
I have an Item, say 112233, and an OrderID, 1001. Order Quantity is 5.
Startdate of Order 1001 is 2025-03-17 and Due Date 2025-03-19.
3 out of 5 on this order is completed on 2025-03-19, i.e on the correct leadtime - but the last 2 cannot be completed due to component shortage.
The Duedate of Order 1001 is then moved to monday next week, 2025-03-24 since the missing component are due and the last 2 individuals on the order can be completed. Since the sales department needs to know when all the individuals on Order 1001 is ready for shipping.
This is where the trouble starts - if the Due Date is unedited then my measure works fine - it calculates the difference between when the order was supposed to be completed (Due Date) and when it was acually completed (Reported Date).
NetWorkdays v2 =
VAR SelectedOrder = MAX( ITEM_TRANSACTION_HISTORY_SUM[Order nr] )
VAR SelectedItem = MAX( ITEM_TRANSACTION_HISTORY_SUM[Item] )
VAR FilteredOrders =
FILTER(
MANUFACTURING_ORDERS,
MANUFACTURING_ORDERS[Order nr] = SelectedOrder &&
MANUFACTURING_ORDERS[Item] = SelectedItem
)
VAR DueDate = MAXX(FilteredOrders, MANUFACTURING_ORDERS[Order Due])
VAR CompletionDate = MAX( ITEM_TRANSACTION_HISTORY_SUM[Date reported] )
VAR Workdays =
IF(
ISBLANK(DueDate) || ISBLANK(CompletionDate),
BLANK(),
CALCULATE(
COUNTROWS('Date'),
'Date'[Date] >= MIN(DueDate, CompletionDate) && 'Date'[Date] <= MAX(DueDate, CompletionDate),
'Date'[ArbetsDag] = 1,
ISBLANK('Date'[Helgdagar]),
NOT( WEEKDAY('Date'[Date],2) = 5 && QUARTER('Date'[Date]) = 1 && WEEKNUM('Date'[Date], 2) >= 4 && WEEKNUM('Date'[Date], 2) <= 13 )
)
)
RETURN
IF(
ISBLANK(Workdays),
BLANK(),
IF(
DueDate = CompletionDate,
0,
IF(
DueDate > CompletionDate,
- (Workdays - 1),
Workdays - 1
)
)
)But when the DueDate is changed, then the calculation runs on the individuals that are already completed aswell and instead of having a leadtime of 2 days (17/3-19/3) it now says that it took 17/3-24/3.
I need help how to be able to evaluate the first 3 individuals on the "original" Due Date (19/3) and the remaining 2 on the "new" Due Date 24/3.
Regards
Fredde86 Try using
DAX
NetWorkdays_v2 =
VAR SelectedOrder = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr])
VAR SelectedItem = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])VAR FilteredOrders =
FILTER(
MANUFACTURING_ORDERS,
MANUFACTURING_ORDERS[Order nr] = SelectedOrder &&
MANUFACTURING_ORDERS[Item] = SelectedItem
)VAR DueDate = MAXX(FilteredOrders, MANUFACTURING_ORDERS[Order Due])
VAR CompletionDates =
FILTER(
ITEM_TRANSACTION_HISTORY_SUM,
ITEM_TRANSACTION_HISTORY_SUM[Order nr] = SelectedOrder &&
ITEM_TRANSACTION_HISTORY_SUM[Item] = SelectedItem
)VAR LeadTimes =
ADDCOLUMNS(
CompletionDates,
"LeadTime",
VAR CompletionDate = ITEM_TRANSACTION_HISTORY_SUM[Date reported]
VAR CurrentDueDate =
CALCULATE(
MAX(MANUFACTURING_ORDERS[Order Due]),
FILTER(
MANUFACTURING_ORDERS,
MANUFACTURING_ORDERS[Order nr] = SelectedOrder &&
MANUFACTURING_ORDERS[Item] = SelectedItem &&
MANUFACTURING_ORDERS[Order Due] <= CompletionDate
)
)
VAR Workdays =
IF(
ISBLANK(CurrentDueDate) || ISBLANK(CompletionDate),
BLANK(),
CALCULATE(
COUNTROWS('Date'),
'Date'[Date] >= MIN(CurrentDueDate, CompletionDate) && 'Date'[Date] <= MAX(CurrentDueDate, CompletionDate),
'Date'[ArbetsDag] = 1,
ISBLANK('Date'[Helgdagar]),
NOT(WEEKDAY('Date'[Date],2) = 5 && QUARTER('Date'[Date]) = 1 && WEEKNUM('Date'[Date], 2) >= 4 && WEEKNUM('Date'[Date], 2) <= 13)
)
)
RETURN
IF(
ISBLANK(Workdays),
BLANK(),
IF(
CurrentDueDate = CompletionDate,
0,
IF(
CurrentDueDate > CompletionDate,
- (Workdays - 1),
Workdays - 1
)
)
)
)VAR TotalLeadTime = SUMX(LeadTimes, [LeadTime])
RETURN
TotalLeadTime
5 Replies
- bhanu_gautamSuper User
Fredde86 Try using
DAX
NetWorkdays_v2 =
VAR SelectedOrder = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr])
VAR SelectedItem = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])VAR FilteredOrders =
FILTER(
MANUFACTURING_ORDERS,
MANUFACTURING_ORDERS[Order nr] = SelectedOrder &&
MANUFACTURING_ORDERS[Item] = SelectedItem
)VAR DueDate = MAXX(FilteredOrders, MANUFACTURING_ORDERS[Order Due])
VAR CompletionDates =
FILTER(
ITEM_TRANSACTION_HISTORY_SUM,
ITEM_TRANSACTION_HISTORY_SUM[Order nr] = SelectedOrder &&
ITEM_TRANSACTION_HISTORY_SUM[Item] = SelectedItem
)VAR LeadTimes =
ADDCOLUMNS(
CompletionDates,
"LeadTime",
VAR CompletionDate = ITEM_TRANSACTION_HISTORY_SUM[Date reported]
VAR CurrentDueDate =
CALCULATE(
MAX(MANUFACTURING_ORDERS[Order Due]),
FILTER(
MANUFACTURING_ORDERS,
MANUFACTURING_ORDERS[Order nr] = SelectedOrder &&
MANUFACTURING_ORDERS[Item] = SelectedItem &&
MANUFACTURING_ORDERS[Order Due] <= CompletionDate
)
)
VAR Workdays =
IF(
ISBLANK(CurrentDueDate) || ISBLANK(CompletionDate),
BLANK(),
CALCULATE(
COUNTROWS('Date'),
'Date'[Date] >= MIN(CurrentDueDate, CompletionDate) && 'Date'[Date] <= MAX(CurrentDueDate, CompletionDate),
'Date'[ArbetsDag] = 1,
ISBLANK('Date'[Helgdagar]),
NOT(WEEKDAY('Date'[Date],2) = 5 && QUARTER('Date'[Date]) = 1 && WEEKNUM('Date'[Date], 2) >= 4 && WEEKNUM('Date'[Date], 2) <= 13)
)
)
RETURN
IF(
ISBLANK(Workdays),
BLANK(),
IF(
CurrentDueDate = CompletionDate,
0,
IF(
CurrentDueDate > CompletionDate,
- (Workdays - 1),
Workdays - 1
)
)
)
)VAR TotalLeadTime = SUMX(LeadTimes, [LeadTime])
RETURN
TotalLeadTime - AnonymousNot applicable
Hi Fredde86,
Thanks for reaching out to the Microsoft fabric community forum.
It looks like you are facing issue in your measure where you want to calculate "first 3 individuals on the "original" Due Date (19/3) and the remaining 2 on the "new" Date". As bhanu_gautam already responded to your query, please go through his response and mark it as solution if it solves your query.
I would also take a moment to thank bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
- AnonymousNot applicable
Hi Fredde86,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.- AnonymousNot applicable
Hi Fredde86,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.