topic Re: Need Help on some DAX command or solution way around my issue in DAX Commands and Tips

Need Help on some DAX command or solution way around my issue

ahmad1111 — Wed, 22 Jan 2025 10:29:46 GMT

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

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'[DAYPOD] <> "Friday" && 'Table1'[DAYPOD] <> "Saturday","OnTime",

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

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

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"

)))))))

Re: Need Help on some DAX command or solution way around my issue

bhanu_gautam — Wed, 22 Jan 2025 10:45:54 GMT

ahmad1111 Create a calculated column to determine if the delivery is on time or not.

dax
OTD =
VAR Priority = 'Table1'[Priority]
VAR Diff = 'Table1'[DIFF]
VAR DayPOD = 'Table1'[DAYPOD]
VAR IsWeekend = IF(DayPOD = "Saturday" || DayPOD = "Sunday", 1, 0)
VAR AdjustedDiff =
IF(Priority <= 3,
Diff - COUNTROWS(
FILTER(
CALENDAR('Table1'[OrderDate], 'Table1'[EventDate]),
WEEKDAY([Date], 2) > 5
)
),
Diff
)
RETURN
IF(
(Priority <= 3 && AdjustedDiff <= 5) ||
(Priority >= 4 && Diff <= 1),
"OnTime",
"NotOnTime"
)

Re: Need Help on some DAX command or solution way around my issue

ahmad1111 — Wed, 22 Jan 2025 10:55:46 GMT

Got error

Re: Need Help on some DAX command or solution way around my issue

bhanu_gautam — Wed, 22 Jan 2025 12:25:34 GMT

ahmad1111 , Check dates mentioned inside calender formular, Start date is after end date

Re: Need Help on some DAX command or solution way around my issue

Anonymous — Thu, 23 Jan 2025 02:13:01 GMT

Hi ahmad1111 ,

Based on the information, try using the following DAX formula.

OTD = VAR Priority = 'Table1'[Priority] VAR DayPOD = 'Table1'[DAYPOD] VAR Diff = 'Table1'[DIFF] VAR IsWeekend = (DayPOD = "Saturday" || DayPOD = "Sunday") VAR OnTimeCondition = SWITCH( TRUE(), Priority <= 3 && Diff <= 5 && !IsWeekend, "OnTime", Priority > 3 && Diff <= 1, "OnTime", TRUE, "NotOnTime" ) RETURN OnTimeCondition

If the DAX formula is wrong or doesn't work, please provide example data.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need Help on some DAX command or solution way around my issue

ahmad1111 — Thu, 23 Jan 2025 04:55:35 GMT

hi bhanu i checked it should be correct but weird thing is if i change OrderDate which i dont want to but i tested and made it date 01.01.2025 manual instead of OrderDate error goes away but i want to keep that orderdate see in my excel sheet if i make it same as today date or close to it it gives that same error again is there a work around this?

Re: Need Help on some DAX command or solution way around my issue

ahmad1111 — Thu, 23 Jan 2025 05:08:26 GMT

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