Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ahmad1111
Frequent Visitor

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

‎01-22-2025 02:29 AM
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"
)))))))
Message 1 of 7
448 Views
0
1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

‎01-22-2025 02:45 AM

@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"
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






View solution in original post

Message 2 of 7
432 Views
6 REPLIES 6
Anonymous
Not applicable

‎01-22-2025 06:13 PM

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.

Message 7 of 7
338 Views
0
bhanu_gautam
Super User
Super User

‎01-22-2025 02:45 AM

@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"
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 7
433 Views
ahmad1111
Frequent Visitor
In response to bhanu_gautam

‎01-22-2025 02:55 AM

ahmad1111_0-1737543336405.png

Got error

Message 3 of 7
425 Views
0
bhanu_gautam
Super User
Super User
In response to ahmad1111

‎01-22-2025 04:25 AM

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 4 of 7
391 Views
0
ahmad1111
Frequent Visitor
In response to bhanu_gautam

‎01-22-2025 09:08 PM

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 OnTimestamp
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/19/2501/21/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/06/2501/07/25
01/05/2501/09/25
01/03/2501/03/25
01/05/2501/09/25
01/21/2501/22/25
01/16/2501/17/25
01/06/2501/07/25
01/19/2501/22/25
01/16/2501/21/25
Message 6 of 7
318 Views
0
ahmad1111
Frequent Visitor
In response to bhanu_gautam

‎01-22-2025 08:55 PM

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?

Message 5 of 7
324 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All