Forum Discussion
HELP with weekend calculation and dax issue
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
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" ) )
4 Replies
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
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
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
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" ) )
