Forum Discussion
Split FTE amount between two dates considering working days
Hi,
I'm trying to work on a gant model that helps me convert FTE in a period
For example, i have this lines :
And I want powerbi to convert the first line for example in 11 lines because there's 11 working days in this period. then, split the ressources equally between this working days and week numbers. that way, i can calculate an amount of average ressources needed for each week and each month.
here's what the convertion of the firts line look like :
11 working day
5 FTE / 11 WD = 0,4545
new structure :
Week ; number Workingday ; FTE
40 07/10/2022 0,4545
41 10/10/2022 0,4545
41 11/10/2022 0,4545
41 12/10/2022 0,4545
41 13/10/2022 0,4545
41 14/10/2022 0,4545
42 17/10/2022 0,4545
42 18/10/2022 0,4545
42 19/10/2022 0,4545
42 20/10/2022 0,4545
42 21/10/2022 0,4545
thank you for helping me
Hello legolasladlf ,
this function will return all the working days between your start and end dates:// fnWORKINGDAYS let func = (StartDate, EndDate, optional Holidays as list, optional StartOfWeek as number) => let /* Debug parameters StartDate = #date(2022,10,7), EndDate = #date(2022,10,23), Holidays = null, StartOfWeek = null, */ // optional StartOfWeek, if empty the week will start on Monaday startOfWeek = if StartOfWeek = null then 1 else StartOfWeek, // cater for empty dates StartEmpty = if StartDate = null then #date(1900,1,1) else StartDate, EndEmpty = if EndDate = null then #date(1900,1,1) else EndDate, // Providing for logic where EndDate is after StartDate Start = List.Min({StartEmpty, EndEmpty}), End = List.Max({StartEmpty, EndEmpty}), // Switch sign if EndDate is before StartDate Sign = if EndEmpty < StartEmpty then -1 else 1, // Get list of dates between Start- and EndDate ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)), // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays; // otherwise continue with previous table DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )), // Select only the first 5 days of the week // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ) in DeleteWeekends ,documentation = [ Documentation.Name = " Date.Workdays.pq ", Documentation.Description = " Returns the dates of the working days between StartDate and EndDate. ", Documentation.LongDescription = " Returns the dates of the working days between StartDate and EndDate. Working days exclude weekends and any dates identified in (optional) holidays. ", Documentation.Category = " Date ", Documentation.Source = " www.TheBIccountant.com . ", Documentation.Version = " 1.0", Documentation.Author = " Imke Feldmann ", Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}] in Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))If you apply it on the rows of your table, you'll get a list of all the relevant dates. Before expanding it, you should add a column that counts the number of rows of this list, as this will be the divisor for the FTE-calculation.
Please let me know if you need further help with implementing this solution.
1 Reply
Hello legolasladlf ,
this function will return all the working days between your start and end dates:// fnWORKINGDAYS let func = (StartDate, EndDate, optional Holidays as list, optional StartOfWeek as number) => let /* Debug parameters StartDate = #date(2022,10,7), EndDate = #date(2022,10,23), Holidays = null, StartOfWeek = null, */ // optional StartOfWeek, if empty the week will start on Monaday startOfWeek = if StartOfWeek = null then 1 else StartOfWeek, // cater for empty dates StartEmpty = if StartDate = null then #date(1900,1,1) else StartDate, EndEmpty = if EndDate = null then #date(1900,1,1) else EndDate, // Providing for logic where EndDate is after StartDate Start = List.Min({StartEmpty, EndEmpty}), End = List.Max({StartEmpty, EndEmpty}), // Switch sign if EndDate is before StartDate Sign = if EndEmpty < StartEmpty then -1 else 1, // Get list of dates between Start- and EndDate ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)), // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays; // otherwise continue with previous table DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )), // Select only the first 5 days of the week // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ) in DeleteWeekends ,documentation = [ Documentation.Name = " Date.Workdays.pq ", Documentation.Description = " Returns the dates of the working days between StartDate and EndDate. ", Documentation.LongDescription = " Returns the dates of the working days between StartDate and EndDate. Working days exclude weekends and any dates identified in (optional) holidays. ", Documentation.Category = " Date ", Documentation.Source = " www.TheBIccountant.com . ", Documentation.Version = " 1.0", Documentation.Author = " Imke Feldmann ", Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}] in Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))If you apply it on the rows of your table, you'll get a list of all the relevant dates. Before expanding it, you should add a column that counts the number of rows of this list, as this will be the divisor for the FTE-calculation.
Please let me know if you need further help with implementing this solution.