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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FrankGaraco
Regular Visitor

undefined

hola amable comunidad

 

Requiero de su apoyo para poder sumar 5 dias habiles (no contar sabados ni domingos) a una fecha tipo DateTimezone

 

Tengo un campo Date con miles de fechas, por ejemplo esta fecha (26/05/2023 6:00:00 PM) al sumar los 5 dias habiles mi fecha de salidad debe ser 02/06/2023 6:00:00 PM

 

Gracias

 

1 REPLY 1
ronrsnfld
Super User
Super User

Your examples are of the DateTime type,  NOT DateTimeZone.  Accordingly, the below code for a Power Query function uses DateTime.  You should be able to adapt it to your actual problem

 

Also, the code is more generalized and includes an optional Holidays list. You can create the list yourself, which, if you have thousands of dates, would be more efficient, as the code will download an appropriate list each time it executes.

 

//Note optional Holidays parameter
//You can substitute a self-generated List of holidays, or use the website in the code to download a list
//  If you use the website, be sure to use a recognized country abbreviation
//If you are using holidays, you should separate this list and buffer the result for speed issues.
//  as written, it will download this list every time the function executes

(start as datetime, numDays as number, optional Country as nullable text) =>

let 
    adjNumDays = Number.RoundAwayFromZero(Number.Abs(numDays*7/5))*2,
    yrStart = Date.Year(start),
    yrEnd = Date.Year(Date.AddDays(start,adjNumDays*Number.Sign(numDays))),

//Create list of country specific Holidays
    yrs = {List.Min({yrEnd,yrStart})..List.Max({yrEnd,yrStart})},

    holidays = if Country = null then {} else    
        List.Accumulate(yrs,{},(state,current) =>
             let 
                Source=Web.Page(Web.Contents("https://www.officeholidays.com/countries/" & Country & "/" & Number.ToText(current))),
                Data0 = Source{0}[Data],
                DateCol = Table.SelectColumns(Data0,"Date"),
                fullDate = List.Transform(DateCol[Date], each Date.FromText(_ & " " & Number.ToText(current)))
            in   
                state & fullDate),

//all Working dates
//Generates a list of working dates in either Ascending or Descending order
//  based on the Sign of numDays
//  starting with the first working date after Start
    allWorkDates = 
        List.RemoveNulls(
            List.Transform(
                List.DateTimes(start, adjNumDays, #duration(Number.Sign(numDays),0,0,0)),
                    each if _ = start then _ 
                            else 
                                if Date.DayOfWeek(_)=Day.Saturday 
                                    or Date.DayOfWeek(_)=Day.Sunday 
                                    or List.Contains(holidays, Date.From(_))
                                then null 
                                else _)),
    addWorkDays = if numDays = 0 then start else allWorkDates{Number.Abs(numDays)}
    
in
    //List.DateTimes(start, adjNumDays, #duration(Number.Sign(numDays),0,0,0))
    //adjNumDays
    addWorkDays

 

Although this is a Power Query forum, if you should want a DAX solution, see direct application of WORKDAY function like in excel 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors