Skip to main content
cancel
Showing results for 
Search instead 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

Reply
legolasladlf
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 :

cpat1.jpg

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
ImkeF
Super User
Super User

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

View solution in original post

1 REPLY 1
ImkeF
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors