topic Re: Power BI query to calculate On time delivery for transportation team in DAX Commands and Tips

Power BI query to calculate On time delivery for transportation team

ahmad1111 — Wed, 29 Jan 2025 12:46:53 GMT

My Code idea here is i have to calculate the instructions which is priortiy and diff is basically

ABS(DATEDIFF(Job[Timestamp],Job[Created On],DAY))

also POD day is

FORMAT('Job'[Timestamp],"dddd")

so what i need is to look for the priority if its 4 and 5 those should deliver in 1 day and that is in weekend is allowed our weekend here is friday and saturday also if its 1 to 3 priority then its only allowed during weekdays sunday to thursday

Created on is basically a column that have my order date and timestamp is my event proof of delivery date listed as well my excel sheet to see my source raw data will be changed when giving the end users and they will keep having new raws added daily so my issue now is if i have my dates created on close to todays date or equal to today date and i increase as well event date timestamp to make it bigger than created on date i get error saying calendar function created on cannot be later than timestamp end date although even if i have both same date like 28/01/2025 it still give the error biggest date i was able to list was 22/01/2025 if i put bigger than that date i get the error (((NOTE I ALWAYS HAVE TIMESTAMP BIGGER LOGICALLY SHOULD BE WORKING)))

calcotd =

VAR Priority = 'Job'[Instructions]

VAR Diff = 'Job'[Diff1]

VAR DayPOD = 'Job'[PODDay]

VAR IsWeekend = IF(DayPOD = "Friday" || DayPOD = "Saturday", 1, 0)

VAR AdjustedDiff =

IF(Priority <= 3,

Diff - COUNTROWS(

FILTER(

CALENDAR('Job'[Created On], 'Job'[Timestamp]),

WEEKDAY([Date], 2) > 5

)

Diff

)

RETURN

IF(

(Priority <= 3 && AdjustedDiff <= 5 && 'Job'[Late/Not] = "NotLate") ||

(Priority >= 4 && Diff <= 1 && 'Job'[Late/Not] = "NotLate"),

"OnTime",

"NotOnTime"

)

Created On

01/22/25

Timestamp

1/22/2025

1/27/2025

1/22/2025

1/27/2025

Re: Power BI query to calculate On time delivery for transportation team

DataNinja777 — Wed, 29 Jan 2025 12:53:33 GMT

Hi ahmad1111 ,

Your Power BI DAX query was encountering errors due to the CALENDAR function, particularly when Created On was later than Timestamp, which logically should not happen since Timestamp represents proof of delivery. The calculation also needed to properly exclude weekends for certain priority levels.

The following revised DAX formula ensures that on-time delivery is calculated correctly:

OnTimeDelivery = VAR Priority = 'Job'[Instructions] VAR CreatedDate = 'Job'[Created On] VAR DeliveredDate = 'Job'[Timestamp] VAR DayPOD = FORMAT(DeliveredDate, "dddd") VAR IsWeekend = IF(DayPOD = "Friday" || DayPOD = "Saturday", 1, 0) VAR RawDiff = ABS(DATEDIFF(CreatedDate, DeliveredDate, DAY)) VAR AdjustedDiff = IF( Priority <= 3, RawDiff - COUNTROWS( FILTER( ADDCOLUMNS( CALENDAR(CreatedDate, DeliveredDate), "WeekdayNum", WEEKDAY([Date], 2) ), [WeekdayNum] >= 6 ) ), RawDiff ) RETURN IF( (Priority <= 3 && AdjustedDiff <= 5) || (Priority >= 4 && RawDiff <= 1), "OnTime", "NotOnTime" )

This formula first extracts the priority level, created date, and timestamp for delivery. The absolute difference in days is then calculated, and an adjustment is made for priorities 1 to 3 by excluding Fridays and Saturdays. The OnTime classification is determined based on whether the adjusted difference falls within the required threshold: for priorities 4 and 5, the delivery must be within one day (including weekends), while for priorities 1 to 3, delivery is required within five working days, excluding weekends. This ensures that the calculation correctly accounts for weekends without triggering the CALENDAR function error, preventing issues when new data is added.

Best regards,

Re: Power BI query to calculate On time delivery for transportation team

ahmad1111 — Thu, 30 Jan 2025 05:00:02 GMT

Hi data ninja thanks for the reply ill try it now at office but see the weird part for me is i put lets say todays date 30/01/2025 for both created on and timestamp delivery date it wont accept it and that error rise up again saying created on is bigger than timestamp end date so i am confused why cant i use todays date

Re: Power BI query to calculate On time delivery for transportation team

Poojara_D12 — Thu, 30 Jan 2025 05:07:33 GMT

Hi ahmad1111

The issue you're facing stems from the logical relationship between the Created On and Timestamp fields. The ABS(DATEDIFF()) function is intended to calculate the difference between these dates, but you’re getting an error when Created On is later than Timestamp, even if the dates are the same. This could be a data quality issue that violates the assumption that Timestamp should always be later. A potential fix involves ensuring that Created On is not later than Timestamp, using ABS to avoid negative values. Additionally, simplifying the weekend logic ensures that you're correctly handling different priorities, where priority 4 and 5 deliveries allow weekends, while priorities 1-3 restrict delivery to weekdays. Finally, adjusting the difference calculation based on the priority ensures that the final measure works as expected, marking "OnTime" or "NotOnTime" without errors. Proper validation of your data and logic is key to resolving this.

Updated Measure:

calcotd = VAR Priority = 'Job'[Instructions] VAR Diff = ABS(DATEDIFF('Job'[Created On], 'Job'[Timestamp], DAY)) VAR DayPOD = 'Job'[PODDay] VAR IsWeekend = IF(DayPOD = "Friday" || DayPOD = "Saturday", 1, 0) VAR AdjustedDiff = IF( Priority <= 3, Diff - COUNTROWS( FILTER( CALENDAR('Job'[Created On], 'Job'[Timestamp]), WEEKDAY([Date], 2) > 5 ) ), Diff ) RETURN IF( (Priority <= 3 && AdjustedDiff <= 5 && 'Job'[Late/Not] = "NotLate") || (Priority >= 4 && Diff <= 1 && 'Job'[Late/Not] = "NotLate"), "OnTime", "NotOnTime" )