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
Anonymous
Not applicable

Function - get previous workday date

I am looking for some power query gurus help here , as am totally unfamiliar with M language . 

 

i would need to create a function to get my previous working day date . 

E.g.

  1. if today is May 31st then expected result is 28th May 2021
  2. if today is 1st june 2021 then expected result it 31st May 

 

appreciate any help here @swat  @mahoneypat @edhans @PhilipTreacy 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Anonymous 

Add the following code as a new custom column: 

let
  today = Date.From(DateTime.LocalNow()), 
  d     = Date.DayOfWeek(today, Day.Monday)
in
  if d = 6 then
    Date.AddDays(today, - 2)
  else if d = 0 then
    Date.AddDays(today, - 3)
  else
    Date.AddDays(today, - 1)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
edhans
Super User
Super User

Here is a cutom function to do that @Anonymous 

(varDate as date) =>
let
    Source = 
        let
            varDayOfWeek = Date.DayOfWeek(varDate, Day.Monday)
        in
        if varDayOfWeek = 0 then Date.AddDays(varDate, -3)
        else if varDayOfWeek = 6 then Date.AddDays(varDate, -2)
        else Date.AddDays(varDate, -1)
in
    Source

 

  1. Create a new blank query
  2. In the advanced editor, remove ALL of the code
  3. Paste the code above into the advanced editor and press Done.
  4. Rename it fnPreviousWorkDay (instead of Query1 or whatever it was called.

Now, add a new column in your data table and use the formula =fnPreviousWorkday([NameOfDateColumn])

edhans_0-1622735882893.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Fowmy
Super User
Super User

@Anonymous 

Add the following code as a new custom column: 

let
  today = Date.From(DateTime.LocalNow()), 
  d     = Date.DayOfWeek(today, Day.Monday)
in
  if d = 6 then
    Date.AddDays(today, - 2)
  else if d = 0 then
    Date.AddDays(today, - 3)
  else
    Date.AddDays(today, - 1)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy super helpful, just wondering about when Monday's are a Holiday - what logic would you use to get the last working day = to the Friday before the holiday? In this case Monday, 1/15/2024 was a Holiday, so I would want my last workday to show 1/12/2024. 
thanks!

there more dynamic way. if you have continuous holidays like monday, tuesday also including weekends, so we need to show as friday as working day.

here code:

 

// Merge the two tables on the 'Date' column to identify holidays
  MergedTables = Table.NestedJoin(#"Converted to Table", "Date", Holiday, "Holiday Date", "HolidayInfo", JoinKind.LeftOuter),
  // Expand the merged column to check if the date is a holiday
  ExpandedTables = Table.ExpandTableColumn(MergedTables, "HolidayInfo", {"Holiday Date"}, {"Holiday Date"}),

  SortedTable = Table.Sort(ExpandedTables, {{"Date", Order.Ascending}}),
  // Function to find the previous working day
  FindPreviousWorkingDay = (date) as date =>
        let
            // Generate a list of previous dates up to 7 days back
            PreviousDates = List.Transform({1..7}, each Date.AddDays(date, -_)),
           
            // Filter out weekends and holidays to find the most recent working day
            WorkingDays = List.Select(PreviousDates, each
                not List.Contains(Holiday[Holiday Date], _) and // Not a holiday
                Date.DayOfWeek(_, Day.Sunday) <> 0 and // Not a Sunday
                Date.DayOfWeek(_, Day.Sunday) <> 6 // Not a Saturday
            )
        in
            List.First(WorkingDays, date),
  // Return the first valid working day or the original date if none found,
  // Add column to compute the final adjusted date
  AdjustedDates = Table.AddColumn(SortedTable, "End of Day", each let
                currentDate = [Date],
                dayOfWeek = Date.DayOfWeek(currentDate, Day.Sunday), // Day of the week (0=Sunday, 6=Saturday)
                isWeekend = (dayOfWeek = 6 or dayOfWeek = 0), // Check if Saturday (6) or Sunday (0)
                isHoliday = [Holiday Date] <> null, // Check if the date is a holiday
               
                // Determine the adjusted date
                adjustedDate =
                    if isWeekend or isHoliday then
                        FindPreviousWorkingDay(currentDate) // Adjust weekends and holidays to the previous working day
                    else
                        currentDate // No adjustment needed for regular weekdays
            in
                adjustedDate),
  #"Transform columns" = Table.TransformColumnTypes(AdjustedDates, {{"Business Day", type date}}),

You'd need a table of holidays. My custom function above can be modified for this and with a bit of recursion, works. I keyed in the holidays as a list, but you could pull them in from a table and convert to a list for this purpose.

(varDate as date) =>
let
    Source = 
        let
            varDayOfWeek = Date.DayOfWeek(varDate, Day.Monday),
            varHolidays = {#date(2024,1,1), #date(2024,1,15)},
            varPreviousWorkDay =
                if varDayOfWeek = 0 then Date.AddDays(varDate, -3)
                else if varDayOfWeek = 6 then Date.AddDays(varDate, -2)
                else Date.AddDays(varDate, -1)
        in
            if List.Contains(varHolidays, varPreviousWorkDay) 
            then fnPreviousWorkday(varPreviousWorkDay) 
            else varPreviousWorkDay
in
    Source

 

You can see that both Jan 2 and Jan 16 should pick Jan 1 and 15, but that Monday is a holiday. So it goes back to Dec 29 and Jan 12 respectively.

edhans_0-1705504482803.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

thanks for this, very helpful!
Anonymous
Not applicable

Thanks @Fowmy  really appreciate your time for responding to my query .

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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