Add days to customer calendar

[danialsj]

I have a custom calendar table which excludes weekends and certain holidays. I want to create a custom column in which I can add X number of days to a date while ignoring the weekends and the holidays. How can I achieve this? 

Below is a rough dataset

Date	DaysToAdd	CalculatedCol
26-Oct	2	29-Oct

Date	Holidays
26-Oct	No
27-Oct	Yes
28-Oct	No
29-Oct	No

[Fowmy · Accepted solution]

@danialsj 

You can try the following calculated column, you can replace _Days variable value with a column value if you need also 365 in the calculation can be extended if necessary.

DaysToAdd = 
VAR __Days = 2
VAR __CURRENTDATE = Data[Date]
VAR __HOLIDAYS = CALCULATETABLE( VALUES(Holiday[Date]) , Holiday[Holidays] = "Yes" , Holiday[Date] >= __CURRENTDATE , Holiday[Date] <= __CURRENTDATE + 365   ) 
VAR __RESULT = 
    MAXX(
        TOPN( 
            __Days+1,
            FILTER(
                CALENDAR( __CURRENTDATE  , __CURRENTDATE + 365 ),
                NOT [Date] IN  __HOLIDAYS
            ),
            [Date],ASC
        ),
        [Date]
    )
RETURN
__RESULT

[amitchandak]

@danialsj , I have a blog on how to deal with this, see if that can help

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...