Forum Discussion
Selecting Earliest Working Date from Date Table Based on Shipping Time
Hi, I have a date table which is a calculated table and contains a column "Is Working Day" with values 0 or 1. The business problem I am trying to solve is that I have an Order Date (which will always be on a working day), and I have a Processing Time in days (it's an integer). I need to calculate the next earliest Ship Date which can only occur on dates where "Is Working Day" == 1.
As an example, say the Order Date is Wednesday, the 13th January, 2021. The Processing Time on this order is 5 working days, so the earliest Ship Date is Wednesday, the 20th January, 2021. This is obviously 7 calendar days from the Order Date, but since it cannot include the 2 days over the weekend where "Is Working Day" == 0, it effectively adds the additional 2 days.
The challenge I'm having, is that even when I filter the date table to where "Is Working Day" == 1 (say, within a CALCULATE function), I cannot just add the 5 to the Order Date, as that will just perform a standard date calculation that will not factor in working days.
What I really want to do is count "up" 6 rows in the filtered table from the Order Date, but I don't know how to do that. I imagine I would need to add an index column to the filtered table, and use that somehow? Again, don't know how to do that.
Any help would be much appreciated!
Hi, Anonymous
Sorry, I thought what you need is the same result, so I made a wrong change in the measure. Just need to modify the measure, the correct result should be like this:
Column = VAR a = ADDCOLUMNS ( 'Dim Date Table', "aa", RANKX ( FILTER ( ALL ( 'Dim Date Table' ), [Is Working Day] = 1 && [Date] >= Orders[Order Date] ), [Date], , ASC ) ) RETURN MAXX ( FILTER ( a, [aa] = [Processing Time] ), [Date])Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- parry2k
Super User
Anonymous can you throw a sample pbix file and share it here thru one drive /google drive. The logic you want to follow is that order date + processing date falls 6 or 7 days (sat/sun) then add 1 or 2 days based on which weekday it is falling and that will get the Monday (working day)
Thansk parry2k . Here is a WeTransfer link to an example file of what I'm trying to do. It's the "Ship Date" field on the Orders table in this file. I wrote some notes in there for you.
- v-janeyg-msft
Community Support
Hi, Anonymous
According to your description,I think you can create a calculate column, use rank function to calculate the desired result.
Like this:
Column = VAR a = ADDCOLUMNS ( 'Dim Date Table', "aa", RANKX ( FILTER ( ALL ( 'Dim Date Table' ), [Is Working Day] = 1 && [Date] >= Orders[Order Date] ), [Date], , ASC ) ) RETURN MAXX ( FILTER ( a, [aa] = [Processing Time] ), [Date] - 1 )If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-janeyg-msft
Hi, Anonymous
Sorry, I thought what you need is the same result, so I made a wrong change in the measure. Just need to modify the measure, the correct result should be like this:
Column = VAR a = ADDCOLUMNS ( 'Dim Date Table', "aa", RANKX ( FILTER ( ALL ( 'Dim Date Table' ), [Is Working Day] = 1 && [Date] >= Orders[Order Date] ), [Date], , ASC ) ) RETURN MAXX ( FILTER ( a, [aa] = [Processing Time] ), [Date])Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
