cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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?

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!

1 ACCEPTED SOLUTION
Super User

@VARUN_SHA , 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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

3 REPLIES 3
Community Champion

``````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``````

Super User

@VARUN_SHA , 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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

New Member

Thanks for the response. It worked.