Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
StuartSmith
Power Participant
Power Participant

Adding X Business\Working Days to another date.

I have a table (Time to Fill) with a date column (Position Opened) and want to create another column on the same table that displays 35 Business\Working Days from the "Position Opened" date, but after lots of searching, etc. cant find another thing.  Was hoping there was a parameter to NETWORKDAYS where you could add days, but nope.

 

Thanks in advance.

1 ACCEPTED SOLUTION

@StuartSmith This depends on your logic. The way this is written, January 8th is working day +0, January 9th is working day +1 (from the Start Date). It seems you want to count the start date as the first working day. In that case, remove the +1 from the CALENDAR function so CALENDAR( [Start Date], [Start Date] + __Days)

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@StuartSmith Here is a solution I came up with in DAX. PBIX is attached below signature.

 

Due Date = 
    VAR __Days = [Work Days] * 4
    VAR __Calendar = 
        FILTER(
            ADDCOLUMNS(
                CALENDAR( [Start Date] + 1, [Start Date] + __Days),
                "WeekDay",WEEKDAY([Date],2)
            ),
            [WeekDay] < 6
        )
    VAR __Table = 
        ADDCOLUMNS(
            __Calendar,
            "Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
        )
    VAR __Result = MAXX(FILTER(__Table, [Index] = [Work Days]),[Date])
RETURN
    __Result

 

And here is a PQ solution:

 

let WorkDayAdd = (Start as date, WorkDays as number) as date =>
    let
        Source = 
            List.Generate( 
                () => Date.AddDays(Start, WorkDays * 4), 
                each _ >= Date.AddDays(Start,1), 
                each Date.AddDays(_, -1 )
            ),
        #"Converted to Table" = 
            Table.FromList(
                Source, 
                Splitter.SplitByNothing(), 
                null, null, ExtraValues.Error
            ),
        #"Added Custom" = 
            Table.AddColumn(
                #"Converted to Table", 
                "WeekDay", each Date.DayOfWeek([Column1],Day.Monday)
            ),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [WeekDay] < 5),
        #"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}),
        #"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type),
        #"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays),
        #"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1"),
        #"Result" = Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
    in
        #"Result"
in
    WorkDayAdd

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks, I have it working, of sorts, but as an example, the start date is Mondays 8th Jan and 35 Business days would be Fri 23rd Feb, but your script is doing Mon 26th Feb.  

 

35 Business Days From Position Opened =
    VAR DaysToFill = 35
    VAR __Days = DaysToFill * 4
    VAR __Calendar =
        FILTER(
            ADDCOLUMNS(
                CALENDAR( [Position Open Date] + 1, [Position Open Date] + __Days),
                "WeekDay",WEEKDAY([Date],2)
            ),
            [WeekDay] < 6
        )
    VAR __Table =
        ADDCOLUMNS(
            __Calendar,
            "Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
        )
    VAR __Result = MAXX(FILTER(__Table, [Index] = DaysToFill),[Date])
RETURN
    __Result

Even you example file does the same...

StuartSmith_0-1712868647651.png

What am i missing?

@StuartSmith This depends on your logic. The way this is written, January 8th is working day +0, January 9th is working day +1 (from the Start Date). It seems you want to count the start date as the first working day. In that case, remove the +1 from the CALENDAR function so CALENDAR( [Start Date], [Start Date] + __Days)

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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