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

[mikeangell]

This solution worked perfectly for me - Thank You.

[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

[mikeangell]

Hi,
I was trying to resolve the same issue but found your solution came up with a date too early as it wasn't including non working days after the end date (but before the revised end date once you've added in the non-working days).
I was using it for 90 days and my first date was 14/11/2025. Your solution came up with 13/3/26.
The correct solution should have been nearer 24/3/26 but because the end date came out at 12/2/26 it excluded the non working days between 12th Feb and 24th March - if that makes sense?!

[Anonymous]

Thanks for the response. It worked.