topic Re: Function that returns next business day based on today's date in DAX Commands and Tips

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

Anonymous — Fri, 29 May 2020 17:18:12 GMT

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

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

AlB — Fri, 29 May 2020 17:57:46 GMT

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

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

MarcelWoodman — Fri, 29 May 2020 17:51:43 GMT

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

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

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

Rickmaurinus — Tue, 25 Jul 2023 16:28:40 GMT

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.

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

michaelu1 — Mon, 03 Apr 2023 15:18:02 GMT

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