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: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
423 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
407 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
313 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
408 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
400 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
366 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
293 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
299 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All