Function that returns next business day based on today's date

[Anonymous]

Example: Today is 05/29, the function should return the next working day will be 06/01.

Does anyone have any idea how this can be done in dax?

thank you

[Rickmaurinus]

For a Power Query solution you can do something like:

= Date.AddDays(
  Date.From( DateTime.LocalNow() ), increase the current date
  1                    // by one day 
   + List.Count(       // + the number of
      List.Generate(   // weekenddays
         () => Date.AddDays( [Date], 1 ),
         each Date.DayOfWeek(_, Day.Monday) >= 5,
         each Date.AddDays(_, 1)
      )
    )
)

For a more robust custom power query function you can also use:

let
  WorkDayOffset = 1,
  AddDays = Number.Sign( WorkDayOffset ),
  NumOfWD = Number.Abs( WorkDayOffset ),
  Holidays = {#date( 2022, 9, 19 )},
  Today = Date.From( DateTime.LocalNow() ),
  ListOfDates =
    List.Generate(
      () => [ Date = Date.AddDays( Today, AddDays ),
              WD_Counter = 0,
              IsWorkday = null
            ],
      each if [WD_Counter] = NumOfWD 
         and [IsWorkday] = true then false else true,
      each  [
              Date       = Date.AddDays( [Date], AddDays ),
              WD_Counter = if Date.DayOfWeek( [Date], 1 ) < 5
                              and not List.Contains( Holidays , [Date] )
                              then [WD_Counter] + 1 else [WD_Counter],
              IsWorkday  = Date.DayOfWeek( [Date], 1 ) < 5
                              and not List.Contains( Holidays, [Date] )
            ],
      each [Date]
    ),
  RelevantWorkDay=  List.Last(  ListOfDates )
in
  RelevantWorkDay

To make sure it respects holidays, you should adjust the 'Holidays' parameter as described here: 

Calculate Nth Business Day From Date in Power Query - BI Gorilla

Hope that helps!

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[MarcelWoodman]

I don't factor in Holidays, but for eliminating the weekends:

if(WEEKDAY([date],2)<5,[date] + 1,[date] + (8 - WEEKDAY([date],2)))

[AlB]

Hi @Anonymous 

Probably best to have a calendar table that has a column indicating whether a day is a business day. Then you could obtain the next business date with something like:

CALCULATE (
    MIN ( CalendarTable[Date] ),
    FILTER (
        ALL ( CalendarTable[Date], CalendarTable[isBusiness] ),
        CalendarTable[Date] > TODAY ()
            && CalendarTable[isBusiness] = "Yes"
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[michaelu1]

This seems to be giving me back tomorrow for every row on my table.