Skip to main content
cancel
Showing results for 
Search instead 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

Reply
VARUN_SHA
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?

 

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!

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
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!





View solution in original post

3 REPLIES 3
AntrikshSharma
Community Champion
Community Champion

@VARUN_SHA 

 

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
Super User
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!





Thanks for the response. It worked.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.