Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
trying to build a funciton that i can give a date and a number of days and it go back this number of days but only counting during week.
For example, if i give 15/01/2020 and 15 it gives me the date 25/12/2019.
Thank you,
Solved! Go to Solution.
Hello @Anonymous
I've created for you this custom function that does this calculation. It has 3 parameters...
Startdate, number to be added (or in case -15 subtracted). As optional parameter a list of holidays could be inputet
// AddDayWithOutWeekendsAndHolidays
(DateStart as date, DayDifference as number, optional Holidays as list) =>
let
HolidayIntern = if Holidays = null then List.Buffer({}) else List.Buffer(Holidays),
CountDirection = if DayDifference<0 then -1 else 1,
CreateListDate = List.Generate
(
()=> [
Dateint=DateStart,
DateDifferenceCount = 0
],
each if CountDirection= -1 then [DateDifferenceCount] >= DayDifference else [DateDifferenceCount] <= DayDifference,
(prevrecord)=> [
Dateint=Date.AddDays(prevrecord[Dateint], (1*CountDirection)),
DateDifferenceCount = if
List.Contains(HolidayIntern,Date.AddDays(prevrecord[Dateint],(1*CountDirection))) or
Date.DayOfWeek(Date.AddDays(prevrecord[Dateint], (1*CountDirection)))>4
then
prevrecord[DateDifferenceCount]
else (prevrecord[DateDifferenceCount]+(1*CountDirection))
],
each [Dateint]
),
FinalResult = if DayDifference = 0 then DateStart else if CountDirection= -1 then List.Min(CreateListDate) else List.Max(CreateListDate)
in
FinalResult
Copy paste this code to the advanced editor and try to invoke the function.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
I've created for you this custom function that does this calculation. It has 3 parameters...
Startdate, number to be added (or in case -15 subtracted). As optional parameter a list of holidays could be inputet
// AddDayWithOutWeekendsAndHolidays
(DateStart as date, DayDifference as number, optional Holidays as list) =>
let
HolidayIntern = if Holidays = null then List.Buffer({}) else List.Buffer(Holidays),
CountDirection = if DayDifference<0 then -1 else 1,
CreateListDate = List.Generate
(
()=> [
Dateint=DateStart,
DateDifferenceCount = 0
],
each if CountDirection= -1 then [DateDifferenceCount] >= DayDifference else [DateDifferenceCount] <= DayDifference,
(prevrecord)=> [
Dateint=Date.AddDays(prevrecord[Dateint], (1*CountDirection)),
DateDifferenceCount = if
List.Contains(HolidayIntern,Date.AddDays(prevrecord[Dateint],(1*CountDirection))) or
Date.DayOfWeek(Date.AddDays(prevrecord[Dateint], (1*CountDirection)))>4
then
prevrecord[DateDifferenceCount]
else (prevrecord[DateDifferenceCount]+(1*CountDirection))
],
each [Dateint]
),
FinalResult = if DayDifference = 0 then DateStart else if CountDirection= -1 then List.Min(CreateListDate) else List.Max(CreateListDate)
in
FinalResult
Copy paste this code to the advanced editor and try to invoke the function.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy