topic Re: To calculate the exact date on 120 working days in future from a certain date value in DAX Commands and Tips

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

Anonymous — Fri, 21 Jun 2024 10:47:39 GMT

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!

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

bhanu_gautam — Fri, 21 Jun 2024 10:56:16 GMT

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

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

AntrikshSharma — Fri, 21 Jun 2024 12:17:41 GMT

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

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

Anonymous — Fri, 21 Jun 2024 13:29:26 GMT

Thanks for the response. It worked.

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

mikeangell — Thu, 26 Mar 2026 13:50:40 GMT

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?!

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

mikeangell — Thu, 26 Mar 2026 13:51:05 GMT

This solution worked perfectly for me - Thank You.