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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION

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

7.png8.png

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.

View solution in original post

6 REPLIES 6
v-janeyg-msft
Community Support
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 )

2.png

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.

Anonymous
Not applicable

Thanks for your help, Janey. Unfortunately it returned the same results. I ended up implemented a solution in SQL which was a little more intuitive for me.

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

7.png8.png

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.

Anonymous
Not applicable

Yep that did the trick, Janey - thank you so much!

parry2k
Super User
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)



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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