Forum Discussion
Daily Operation Job Count Plan
Hi I am new to Power BI and this group, so apologies if this is a daft question but help would be appreciated, thank you. I have a set of data that shows daily orders booked in by operation. I also...
Hi
Another solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAASJkBsDsSWQGwMxGZgsVgdoBojINMUiI2gUsZQtglYPDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operation Number" = _t, Capacity = _t, #"08/18/2023" = _t, #"08/19/2023" = _t, #"08/20/2023" = _t, #"08/21/2023" = _t, #"08/22/2023" = _t, #"08/23/2023" = _t]),
Unpivoted = Table.UnpivotOtherColumns(Source, {"Operation Number", "Capacity"}, "Date", "Order Count"),
Date = Table.TransformColumns(Unpivoted,{{"Date", each Date.FromText(_,[Format="MM/dd/yyyy"]), type date}}),
Types = Table.TransformColumnTypes(Date,{{"Capacity", Int64.Type}, {"Order Count", Int64.Type}}),
AutoJoin = Table.NestedJoin(Types, {"Operation Number"}, Types, {"Operation Number"}, "Data", JoinKind.LeftOuter),
Add_Total_Operating_Number = Table.AddColumn(AutoJoin, "Operation Number Total", each List.Sum([Data][Order Count])),
Expand = Table.ExpandTableColumn(Add_Total_Operating_Number, "Data", {"Capacity", "Date", "Order Count"}, {"Capacity.1", "Date.1", "Order Count.1"}),
Filter = Table.SelectRows(Expand, each [Date.1] <= [Date]),
Group = Table.Group(Filter, {"Operation Number", "Capacity", "Date", "Order Count","Operation Number Total"}, {{"Running_Capacity", each List.Sum([Capacity.1]), Int64.Type}, {"Running_Order", each List.Sum([Order Count.1]), Int64.Type}}),
Add_New_Daily = Table.AddColumn(Group, "New Daily",
each List.Max({0,[Running_Order]-[Running_Capacity]+[Capacity]})),
Add_Pulled_Ahead_Count = Table.AddColumn(Add_New_Daily, "Pulled Ahead Count",
each List.Min({[Capacity],[Running_Capacity]-[Running_Order], List.Max({0,[Operation Number Total]+[Capacity]-[Running_Capacity]})})),
Add_Maximized_Daily_Count = Table.AddColumn(Add_Pulled_Ahead_Count, "Maximized Daily Count", each Text.From([New Daily]) &"/"& Text.From([Pulled Ahead Count]), type text),
Columns = Table.SelectColumns(Add_Maximized_Daily_Count,{"Operation Number", "Capacity", "Date", "Maximized Daily Count"}),
Pivot = Table.Pivot(Table.TransformColumnTypes(Columns, {{"Date", type text}}, "fr"), List.Distinct(Table.TransformColumnTypes(Columns, {{"Date", type text}}, "fr")[Date]), "Date", "Maximized Daily Count")
in
Pivot
Stéphane
Thank you