Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Project | Resource Role | Status | Start Date | End Date | Requested Hours |
ABCD2021 | CTA | Ready to Staff | 23/11/2020 | 31/12/2020 | 48.5 |
ABCD2022 | RM | Ready to Staff | 23/11/2020 | 31/12/2020 | 2 |
I want the output as per below table
Project Code | Resource Role | Status | Start Date | End Date | Requested Hours in Stage | Time Period | Time Period Start | Time Period End | workdays | Hours |
ABCD2021 | CTA | Ready to Staff | 23/11/2020 | 31/12/2020 | 48.5 | 30/11/2020 | 23/11/2020 | 30/11/2020 | 6 | 10.03448 |
ABCD2022 | CTA | Ready to Staff | 23/11/2020 | 31/12/2020 | 48.5 | 31/12/2020 | 1/12/2020 | 31/12/2020 | 23 | 38.46552 |
ABCD2023 | RM | Ready to Staff | 23/11/2020 | 31/12/2020 | 2 | 30/11/2020 | 23/11/2020 | 30/11/2020 | 6 | 0.413793 |
ABCD2024 | RM | Ready to Staff | 23/11/2020 | 31/12/2020 | 2 | 31/12/2020 | 1/12/2020 | 31/12/2020 | 23 | 1.586207 |
Thanks in Advance for your solution
Regards
MD
Solved! Go to Solution.
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"
|
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. |
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"
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! |
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"
|
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