cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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
else if d = 0 then
else
Did I answer your question? Mark my post as a solution! and hit thumbs up
6 REPLIES 6
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)
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])

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
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
else if d = 0 then
else
Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

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

Super User

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

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.