To calculate the exact date on 120 working days in future from a certain date value

[Anonymous]

I am relatively new to Power BI and struggling to implement a requirement in my Power BI Dashboard.

I want to implement a certain feature using DAX.

I have a column "Open Date" (date/time type) in one table "Table1" and i want to create a new column "Date after 120 days" in the same table which should give me the exact date after 120 days (Open Date +120 days) but should exclude non-working days during the calculation.

To be specific,

Date after 120 days = Open Date + 120 (working days).

How can i achieve this?

Additional information:

I already have another table "Calendar" which has column "Dates" (showing all the dates until 31st December 2025) and column "Is Working Day" (Boolean Type) which has value True for working day and False for non-working day against each Date.

Can this "Calendar" table be used to achieve the above requirement?

Any suggestions will be appreciated!

[AntrikshSharma]

@Anonymous 

VAR CurrentDate = 
    Sales[Order Date]
VAR WorkingDates = 
    CALCULATETABLE ( 
        VALUES ( Dates[Date] ),
        Dates[Date] >= CurrentDate,
        Dates[Working Day] = TRUE()
    )
VAR FutureDate = 
    INDEX ( 
        120,
        WorkingDates,
        ORDERBY ( Dates[Date], ASC )
    )
RETURN FutureDate

a slightly faster variant:

VAR CurrentDate = 
    Sales[Order Date]
VAR WorkingDates = 
    FILTER ( 
        ALL ( Dates[Date], Dates[Working Day] ),
        Dates[Date] >= CurrentDate
            && Dates[Working Day] = TRUE()
    )
VAR FutureDate = 
    SELECTCOLUMNS ( 
        INDEX ( 
            120,
            WorkingDates,
            ORDERBY ( Dates[Date], ASC )
        ),
        Dates[Date]
    )
RETURN FutureDate

[bhanu_gautam · Accepted solution]

@Anonymous , Yes you can use Calender table for this just make sure relation is there between both tables and then you can create a calculated column using formula

Date after 120 days =
VAR StartDate = 'Table1'[Open Date]
VAR WorkingDays = 120
VAR EndDate = StartDate + WorkingDays
VAR NonWorkingDays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[Dates] >= StartDate && 'Calendar'[Dates] <= EndDate && 'Calendar'[Is Working Day] = FALSE)
RETURN
EndDate + NonWorkingDays

[Anonymous]

Thanks for the response. It worked.