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

Regular Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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
``````

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Super User

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
``````

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!