Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Thank you for helping us as a super user

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

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

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

AlB
Super User
Super User

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.

 

Thank you for helping us as a super user

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors