Split FTE amount between two dates considering working days

[legolasladlf]

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

[ImkeF · Accepted solution]

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.