Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
69 | |
68 | |
40 | |
29 | |
26 |
User | Count |
---|---|
89 | |
49 | |
44 | |
38 | |
37 |