Power Query - list dates forward, but excluding weekends

[sachintandon84]

How do I solve the attached problem.

I've been given the number of holidays requested by employees and their start request date, and would like to roll the dates forward by individual day, but exclude weekends - or if there is a more flexible way of excluding specific dates, like sat, sun, and mon, or sat, sun, and public holidays that would be great too.

See attached. LHS has report I have, RHS shows the kind of result I would like.

Please help!

https://www.dropbox.com/s/d8dgeuhsqdlslgt/Screenshot%202019-12-09%20at%2001.06.16.png?dl=0

https://www.dropbox.com/s/lb29oqdc6ilyf53/PQ.xlsx?dl=0

Sachin

[v-frfei-msft]

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.

[sachintandon84]

Thanks Frank!

This is really, really helpful, though I am looking for a PQ solution, as I need to do this in Excel.

Any ideas on how I could do this with M-code?

Unless I use Power Pivot in some way?

Are you able to attach / provide a link to your DAX solution?  it might help me think of an M-Code solution for this problem

If you have the solution in Excel, that would also really help!

Sachin

[Mariusz · Accepted solution]

Hi @sachintandon84 

Please see the attached solution containing two extra tables, holiday and dates.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[CD_75]

Thank you - this was exectly what I was looking for. 

[sachintandon84]

Thanks Mariusz, 

This is a very clever solution, and will help me a lot!

Thank you again!

Kind regards, 

Sachin

[Mariusz]

Hi @sachintandon84 

No problem, happy to help!

Many Thanks

Mariusz

[Jimmy801]

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

[sachintandon84]

Hi Jimmy,

Yes, am looking for an Excel solution for this.

I'm very familiar with M-Code, but couldn't work out a PQ solution for it.

Any ideas on how to do it?

Sachin