Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
appreciate any help here @swat @mahoneypat @edhans @PhilipTreacy
Solved! Go to Solution.
@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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Now, add a new column in your data table and use the formula =fnPreviousWorkday([NameOfDateColumn])
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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)
⭕ 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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.