Mildly complex date difference formula

DAX DAX Calculations
1

Hello - I am trying to solve the classic Due Date vs Actual Ship Date question for my shipping department (on time rates).

There are two catches: One, we do not ship on weekends so the formula needs to account for that.

And secondly, the retail channel we are shipping to gets a +3 day buffer allowance. So a shipment that was Due to ship on Jan 15, but actually shipped on Jan 18, is still considered On Time.

I’ve tried DateDiff but it does not work since many of the actual dates precede the due date (early shipments).

I do have a date table. Relationship to the date table is to the Due Date on the Orders table, and the Date Shipped on the Shipments table. But no idea how to account for weekdays only, while also accounting for the +3 day buffer. Help wanted!

2

Hi @richmont,

Can you please share your sample model/excel sheet. It should be straight forward I think as you can have weekdays column in your date table and you can utilize that column.

3

Hi - Yes, but unfortunately I am on a Mac right now…and my PBIX is on my laptop which is not with me. But yes, I have a weekday column in my data model in the Dates table. Both a Monday, Tuesday…etc column, as well as a weekday/weekend column indicated by 0 or 1…and I also have a True False column (weekday = true).

4

Hi @richmont,

No worries, I can just give you a hint then :slight_smile:

You can do followings:

  1. Create calculated column [Workday Not Holiday] in Date table. Make it 1 if it is workday and 0 if it is weekend.
  2. Create calculated column in your orders table etc where you count no. of days between order and delivery.
    image
    image728×302 13.7 KB
  3. From here, you can easily pick up which order are late etc.

You can take below example how to create calculated columns for workday/public holiday etc from this .pbix file.

1 Like
5

Hi @richmont, we’ve noticed that no response has been received from you since 21st of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

Hello - Still not solved yet.

This measure does not seem to work as it does not properly filter items into a Late or On Time segmentation.
New On Time Late Column =

VAR _due = SELECTEDVALUE('Flu Shipped'[Due Date])

VAR _shipped = SELECTEDVALUE('Flu Shipped'[Date Shipped])

VAR _datediff = 
if(_shipped<_due,"on time",if(CALCULATE(COUNTROWS(DateTable),FILTER(DateTable,DateTable[Date]>=_due&&DateTable[Date]<=_shipped&&WEEKDAY([Date],1)<6))<=0,"On Time","Late" ) )

RETURN _datediff
7

I’m having a look at this, but it’s difficult to really find a solution without seeing more around your data and your data model. I think that is where the disconnect is here.

My recommendation is to set up a demo file of your exact scenario, that way it will be easier to find the exact solution you might require.

Also it’s not clear how you want to visualise the information in your report. Not understanding the context required here also is a problem.

This could be solved in the model or in the formula, I just can’t tell at this point.

Thanks
Sam

1 Like
8

Thanks for posting your question @richmont. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, a screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

1 Like