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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

Power Query - list dates forward, but excluding weekends

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

1 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.

 

View solution in original post

8 REPLIES 8
v-frfei-msft
Community Support
Community Support

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])

Capture.PNG

 

For more details, please check the pbix as attached.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

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
Frequent Visitor

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

Thanks Mariusz, 

 

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

 

Thank you again!

 

Kind regards, 

 

Sachin

Hi @sachintandon84 

No problem, happy to help!


Many Thanks

Mariusz

Jimmy801
Community Champion
Community Champion

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors