Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ahmad1111
Frequent Visitor

HELP with weekend calculation and dax issue

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"
))))))))
2 ACCEPTED SOLUTIONS
BeaBF
Super User
Super User

@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

 

View solution in original post

Poojara_D12
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

View solution in original post

4 REPLIES 4
Poojara_D12
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
BeaBF
Super User
Super User

@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

 

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

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors