cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmuralli1829
Frequent Visitor

Split values into months based on working days for each month between 2 dates

Hi I need you help

 

 I want the split the Hours based on number of working days in a month for all months between 2 dates

 

Sample table:

ProjectResource RoleStatusStart DateEnd DateRequested Hours
ABCD2021CTAReady to Staff23/11/202031/12/202048.5
ABCD2022RMReady to Staff23/11/202031/12/20202

 

I want the output as per below table

 

Project CodeResource RoleStatusStart DateEnd DateRequested Hours in StageTime PeriodTime Period StartTime Period EndworkdaysHours
ABCD2021CTAReady to Staff23/11/202031/12/202048.530/11/202023/11/202030/11/2020610.03448
ABCD2022CTAReady to Staff23/11/202031/12/202048.531/12/20201/12/202031/12/20202338.46552
ABCD2023RMReady to Staff23/11/202031/12/2020230/11/202023/11/202030/11/202060.413793
ABCD2024RMReady to Staff23/11/202031/12/2020231/12/20201/12/202031/12/2020231.586207

 

Thanks in Advance for your solution

Regards

MD

2 ACCEPTED SOLUTIONS
AlB
Super User III
Super User III

Hi @dmuralli1829 

Here is a first step to get you to "Time Period Start"  and "Time Period End". For workdays you'll need a list with what days are workdays in the period. The last column (Hours) I do not understand.Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydjEyMDJU0lFyDnEEkkGpiSmVCiX5CsEliWlpQAEjY31DQ32gGgMQx1Tf0AjGMbHQM1WK1YEbYgTS7kvQDGNDJDOMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t, #"Requested Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}, {"Requested Hours", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Period Start", each let aux_ = [End Date] in List.Generate(() => [Start Date], each Duration.Days(Duration.From(_ - aux_))  < 0 , each Date.StartOfMonth(Date.AddMonths(_,1)))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Time Period Start"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Time Period End", each List.Min({Date.EndOfMonth([Time Period Start]), [End Date]})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Time Period Start", type date}, {"Time Period End", type date}})
in
    #"Changed Type1"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

dmuralli1829
Frequent Visitor

Thank you for helping us as a super user

View solution in original post

3 REPLIES 3
CNENFRNL
Super User III
Super User III

A generic solution applicable to a flexible span of period, e.g. 2020/11/8 to 2021/1/16,

let
    #"Total Workdays" = (#"Start Date" as date, #"End Date" as date) => List.Accumulate({0..Duration.Days(#"End Date"-#"Start Date")},0,(s,c)=>if Date.DayOfWeek(Date.AddDays(#"Start Date",c),Day.Monday)<5 then s+1 else s),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydjEyMDJU0lFyDnEEkkGpiSmVCiX5CsEliWlpQAEjY31DQ32gGgMgx9hQ39AIxjGx0DNVitWBG2IE0u5LmhlGYANCXINDUF0ApC2Q9Bia6YPZIHdaWijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t, #"Requested Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}, {"Requested Hours", type number}},"en-GB"),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each Table.FromRows(
            List.Accumulate(
                {1..(Date.Year([End Date])-Date.Year([Start Date]))*12+Date.Month([End Date])-Date.Month([Start Date])}, 
                {{[Start Date], List.Min({[End Date], Date.EndOfMonth([Start Date])})}}, 
                (s,c) => let next = Date.AddMonths([Start Date],c) in s & {{Date.StartOfMonth(next), List.Min({[End Date], Date.EndOfMonth(next)})}}
            ),
            {"Period Start", "Period End"}
        )
    ),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Period Start", "Period End"}, {"Period Start", "Period End"}),
    #"Added Workdays" = Table.AddColumn(#"Expanded Custom", "Workdays", each #"Total Workdays"([Period Start], [Period End])),
    #"Added Hours" = Table.AddColumn(#"Added Workdays", "Hours", each let total = #"Total Workdays"([Start Date], [End Date]) in Number.Round([Requested Hours]*[Workdays]/total,2))
in
    #"Added Hours"

Screenshot 2021-05-14 223236.png

 

AlB
Super User III
Super User III

Hi @dmuralli1829 

Here is a first step to get you to "Time Period Start"  and "Time Period End". For workdays you'll need a list with what days are workdays in the period. The last column (Hours) I do not understand.Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydjEyMDJU0lFyDnEEkkGpiSmVCiX5CsEliWlpQAEjY31DQ32gGgMQx1Tf0AjGMbHQM1WK1YEbYgTS7kvQDGNDJDOMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t, #"Requested Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}, {"Requested Hours", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Period Start", each let aux_ = [End Date] in List.Generate(() => [Start Date], each Duration.Days(Duration.From(_ - aux_))  < 0 , each Date.StartOfMonth(Date.AddMonths(_,1)))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Time Period Start"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Time Period End", each List.Min({Date.EndOfMonth([Time Period Start]), [End Date]})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Time Period Start", type date}, {"Time Period End", type date}})
in
    #"Changed Type1"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

dmuralli1829
Frequent Visitor

Thank you for helping us as a super user

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors