Power Query - list dates forward, but excluding weekends

Hello sachintandon84 

I assume you need the solution in Excel, right?

Do you have expierience with M-language? If you put a similar function that I created once here as an Idea how it could work, are you able to adapt it by your own?

Bye

Jimmy

Hi sachintandon84 ,

Networkdays cannot be used in power bi currently, here I have created a sample by DAX to work around. Please check the following steps as below.

1. Create a date tbale and insert an index column in it.

Date = CALENDARAUTO()

RANKX = RANKX(FILTER(ALL('Date'),WEEKDAY('Date'[Date],2)<=5),'Date'[Date],,ASC,Dense)

2. After that, new a calculated column in our fact table.

enddate = 
VAR IND =
    CALCULATE (
        MAX ( 'Date'[RANKX] ),
        FILTER ( 'Date', 'Date'[Date] = 'Table'[Start date] )
    )
var days = IF('Table'[Days Requestd]<1,1,'Table'[Days Requestd])
VAR ind2 =
    INT(  IND + days )
RETURN
    CALCULATE ( MAX ( 'Date'[Date] ), FILTER ( 'Date', 'Date'[RANKX] = ind2 ) )

3. In the end, we can get our excepted reuslt by the following formula.

Table 2 = 
VAR k =
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Days", 'Table'[Days Requestd] ),
            FILTER ( CALENDARAUTO (), WEEKDAY ( [Date], 2 ) <= 5 )
        ),
        "nam", [Name]
    )
VAR c =
    ADDCOLUMNS (
        k,
        "stad", CALCULATE (
            MAX ( 'Table'[Start date] ),
            FILTER ( 'Table', 'Table'[Name] = [nam] )
        ),
        "edd", CALCULATE (
            MAX ( 'Table'[enddate] ),
            FILTER ( 'Table', 'Table'[Name] = [nam] )
        )
    )
VAR re =
    ADDCOLUMNS ( c, "if", IF ( [stad] <= [Date] && [edd] > [Date], 1, BLANK () ) )
RETURN
SELECTCOLUMNS(    FILTER ( re, [if] = 1 ),"Na_",[Name],"day",[Days],"Date",[Date])

For more details, please check the pbix as attached.