Add Work Days with Custom Calendar

[datachick2024]

Hi, 

I have a custom holiday calendar for multiple sites (they each have their own holiday schedule), and I need to be able to create an estimated ship date based on number of working days. Depending on the situation, it is either a flat 10 WD or based on the item being shipped. The number of WD is a column in the Fact table and the calendars are obviously in a separate Dim table. 

This is a sample structure of what my tables look like - I need to calculate the yellow: 

I found this solution, but it only gets me part of the way there: https://community.fabric.microsoft.com/t5/Desktop/Add-Working-Days-after-Time/m-p/4108448 

Can anyone help? Thanks. 

[amitchandak]

@datachick2024 , Please check my blog, if that can help
https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/Travelling-Across-Workdays-Decodin...

Solution for column is in comment.

[datachick2024]

This is helpful, thank you. However, I need to be able to make the "10" to a dynamic value as well

[Anonymous · Accepted solution]

Hi @datachick2024 ,

Thanks for amitchandak's reply!
And @datachick2024 , here is my sample data (To save time, I use 45702Site X for all Date_Site_Key here):

Because I don't know what your data model looks like, I didn't create Dim Date Table.

Then I use this DAX to create a measure:

Est_Ship_Date = 
VAR OrderDate = SELECTEDVALUE('End Date'[Order Date])
VAR CurrentSite = SELECTEDVALUE('End Date'[Site])
VAR DelayWD = SELECTEDVALUE('End Date'[Shipment_Delay_WD])
VAR CurrentKey = SELECTEDVALUE('End Date'[Date_Site_Key])

VAR WorkingDaysAfterOrder = 
    CALCULATETABLE(
        ADDCOLUMNS(
            FILTER(
                'Working Day',
                'Working Day'[Site] = CurrentSite &&
                'Working Day'[Date_Site_Key] = CurrentKey &&
                'Working Day'[Date] >= OrderDate &&
                'Working Day'[Working Day] = "Yes"
            ),
            "CumulativeWorkDays",
            RANKX(
                FILTER(
                    'Working Day',
                    'Working Day'[Site] = CurrentSite &&
                    'Working Day'[Date_Site_Key] = CurrentKey &&
                    'Working Day'[Date] >= OrderDate &&
                    'Working Day'[Working Day] = "Yes"
                ),
                'Working Day'[Date],
                ,
                ASC
            )
        )
    )

VAR EstimatedDate = 
    MAXX(
        FILTER(
            WorkingDaysAfterOrder,
            [CumulativeWorkDays] = DelayWD
        ),
        'Working Day'[Date]
    )

RETURN
    EstimatedDate

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.