HELP with weekend calculation and dax issue

[ahmad1111]

Hi so i am trying to do some calculation for transportation operation team and i got stuck with something bit hard for me also to note i tried switch it wont work and gives wrong value and things going fine with the nested IF anyways priority i have 1&2 is normal 3 is hot taxi and 4&5 is emergency also DAYPOD basically is giving me based on the event date i have instead of date format of 12/12/2024 for ex it change it to its day and for DIFF is the difference in days between event date - creation of order date and so my intended final result i want is to calculate what is on time and what is not on time based on if 

ALSO NOTE LateOrNotLatePOC which have the code 

Table1[DIFF2] <= 5 && Table1[Priority] <= 4 && Table1[Custom] = "Pending" || Table1[Custom] = "FCN","NotLate","Late"

I need to check if its 1&2&3 priority then allowed delivery days is 5 not more and should not include/Skip weekend then its on time otherwise if its more than 5 days its not on time so in short 5 days skipping weekend if i get order thursday then it should calculate it like thus,sun,mon,tue,wed (5)

For 4&5 priority then it should include weekend

(((NOTE THAT priority was in my data source column and rest was custom column created by me)))

OTD = IF(

'Table1'[Priority] <= 2 && 'Table1'[DIFF] <= 5 && 'Table1'[LateOrNotLatePOC] = "NotLate","OnTime",

IF('Table1'[Priority] > 2 && 'Table1'[DIFF] <= 1,"OnTime",

IF('Table1'[Priority] <= 2 && 'Table1'[DIFF] > 5,"NotOnTime",

IF('Table1'[Priority] <= 2 && 'Table1'[DIFF] <= 5,"OnTime",

IF('Table1'[Priority] = 3 && 'Table1'[DIFF] > 1,"NotOnTime",

IF('Table1'[Priority] >= 4 && 'Table1'[DIFF] > 1,"NotOnTime",

IF('Table1'[Priority] >= 3 && 'Table1'[DIFF] < 1,"OnTime",

IF('Table1'[Priority] <= 2 && 'Table1'[DIFF] < 1,"OnTime"

))))))))

[BeaBF · Accepted solution]

@ahmad1111 Try with:

OTD_Final =
VAR OrderDate = Table1[OrderDate]
VAR EventDate = Table1[EventDate]
VAR Priority = Table1[Priority]
VAR DaysDifference = DATEDIFF(OrderDate, EventDate, DAY)
VAR AdjustedDays =
SWITCH(
TRUE(),
Priority IN {1, 2, 3},
CALCULATE(
COUNTROWS(
FILTER(
CALENDAR(OrderDate, EventDate),
WEEKDAY([Date], 2) <= 5
)
)
),
Priority IN {4, 5}, DaysDifference,
DaysDifference
)

RETURN
SWITCH(
TRUE(),
Priority <= 2 && AdjustedDays <= 5, "OnTime",
Priority = 3 && AdjustedDays <= 1, "OnTime",
Priority >= 4 && DaysDifference <= 1, "OnTime",
"NotOnTime"
)

BBF

[Poojara_D12 · Accepted solution]

Hi @ahmad1111 

Can you please try with this dax:

OTD = 
IF(
    'Table1'[Priority] <= 3 &&
    'Table1'[AdjustedDIFF] <= 5 &&
    'Table1'[LateOrNotLatePOC] = "NotLate",
    "OnTime",
    IF(
        'Table1'[Priority] >= 4 &&
        'Table1'[DIFF] <= 1 &&
        'Table1'[LateOrNotLatePOC] = "NotLate",
        "OnTime",
        "NotOnTime"
    )
)

[BeaBF · Accepted solution]

@ahmad1111 Try with:

OTD_Final =
VAR OrderDate = Table1[OrderDate]
VAR EventDate = Table1[EventDate]
VAR Priority = Table1[Priority]
VAR DaysDifference = DATEDIFF(OrderDate, EventDate, DAY)
VAR AdjustedDays =
SWITCH(
TRUE(),
Priority IN {1, 2, 3},
CALCULATE(
COUNTROWS(
FILTER(
CALENDAR(OrderDate, EventDate),
WEEKDAY([Date], 2) <= 5
)
)
),
Priority IN {4, 5}, DaysDifference,
DaysDifference
)

RETURN
SWITCH(
TRUE(),
Priority <= 2 && AdjustedDays <= 5, "OnTime",
Priority = 3 && AdjustedDays <= 1, "OnTime",
Priority >= 4 && DaysDifference <= 1, "OnTime",
"NotOnTime"
)

BBF

[ahmad1111]

my dates i am testing with currently is as follows Created On is the Order Date and Timestamp is eventdate note that is my data source this from excel 

Created On	Timestamp
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/19/25	01/21/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/06/25	01/07/25
01/05/25	01/09/25
01/03/25	01/03/25
01/05/25	01/09/25
01/21/25	01/22/25
01/16/25	01/17/25
01/06/25	01/07/25
01/19/25	01/22/25
01/16/25	01/21/25

[ahmad1111]

Hi BeaBF thanks for your reply i have though small issue so i get an error saying that calendar function start date cannot be later than end date so in my data source excel sheet if i lets say have dates which is close to todays date or same it somehow give this error and error goes away if i manual put date () 01.01.2025 so can you help on that part