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

How to disaggregate production order in hourly buckets

Hi all,

I have got a complex problem to solve.

I have got below production orders starting in one month and ending in another month. I need to separate out the production requirements between months. So I have to disaggregate them by hours preferably. 

ResourceMaterialStart DateTimeFinish DateTimeDuration (hours)Order QtyOutput/hr
Line11185130/11/2021 23:30:0001/12/2021 04:00:004.5012,350                            2,744
Line11185201/12/2021 04:45:0001/12/2021 07:30:002.758000

                            2,909

 

I need the below table as output:

ResourceMaterialStart DateTimeFinish DateTimeOutput/hr
Line11185130/11/2021 23:301/12/2021 0:00          1,372
Line1118511/12/2021 0:001/12/2021 1:00          2,744
Line1118511/12/2021 1:001/12/2021 2:00          2,744
Line1118511/12/2021 2:001/12/2021 3:00          2,744
Line1118511/12/2021 3:001/12/2021 4:00          2,744
Line1118521/12/2021 4:451/12/2021 5:00             727
Line1118521/12/2021 5:001/12/2021 6:00          2,909
Line1118521/12/2021 6:001/12/2021 7:00          2,909
Line1118521/12/2021 7:001/12/2021 8:00          1,455

Can we do this in power query and also in dax. I need to solve this in both methods. I can then judge which method is better suited to my data model and file.

Any support and help is appreciated.

Regards,

Abrar 

1 ACCEPTED SOLUTION
KNP
Super User
Super User

Ok, I think I understand what you're after.

I have a similar process which I have briefly adapted to your scenario so it won't be perfect yet.

Paste the below into a blank query in the advanced editor and examine the steps.

Come back to me with any questions you have.

As far as DAX goes. I don't believe DAX is the right place to do this type of data modelling. Others may disagree.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZY0xDoAwDAO/gjJXrZ0mKvAGflB1ZGDh/yOFSkiI6eKTYtcq23HulCDk7DczEpkUyknzmrEC3YKJOiysq2Et+g1qyM+hxUxa+Jbq7938X1reKY3FO2aMtGCR1i4=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Resource = _t,
        Material = _t,
        StartDateTime = _t,
        FinishDateTime = _t,
        DurationHours = _t,
        OrderQty = _t,
        OutputHour = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Resource", type text},
      {"Material", Int64.Type},
      {"StartDateTime", type datetime},
      {"FinishDateTime", type datetime},
      {"DurationHours", type number},
      {"OrderQty", Int64.Type},
      {"OutputHour", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "QtyHour",
    each [OrderQty] / (Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24)
  ),
  #"Duplicated Column" = Table.DuplicateColumn(
    #"Added Custom",
    "StartDateTime",
    "StartDate"
  ),
  #"Duplicated Column1" = Table.DuplicateColumn(
    #"Duplicated Column",
    "FinishDateTime",
    "FinishDate"
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Duplicated Column1",
    {
      {"StartDate", type date},
      {"FinishDate", type date}
    }
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type2",
    "DaysBetween",
    each {Number.From([StartDate]) .. Number.From([FinishDate])}
  ),
  #"Expanded DaysBetween" = Table.ExpandListColumn(#"Added Custom1", "DaysBetween"),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Expanded DaysBetween",
    {{"DaysBetween", type datetime}}
  ),
  #"Added Custom3" = Table.AddColumn(
    #"Changed Type3",
    "HoursDay",
    each
      if DateTime.Date([DaysBetween])
        = [StartDate] and DateTime.Date([DaysBetween])
        = [FinishDate]
      then
        Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24
      else if DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [DaysBetween]) * 24
      else if DateTime.Date([DaysBetween]) = [StartDate] then
        Duration.TotalDays(Date.AddDays([DaysBetween], 1) - [StartDateTime]) * 24
      else
        24
  ),
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom3",
    "QtyDay",
    each [HoursDay] * [QtyHour]
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Added Custom2",
    {{"QtyDay", type number}}
  )
in
  #"Changed Type1"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

4 REPLIES 4
KNP
Super User
Super User

Ok, I think I understand what you're after.

I have a similar process which I have briefly adapted to your scenario so it won't be perfect yet.

Paste the below into a blank query in the advanced editor and examine the steps.

Come back to me with any questions you have.

As far as DAX goes. I don't believe DAX is the right place to do this type of data modelling. Others may disagree.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZY0xDoAwDAO/gjJXrZ0mKvAGflB1ZGDh/yOFSkiI6eKTYtcq23HulCDk7DczEpkUyknzmrEC3YKJOiysq2Et+g1qyM+hxUxa+Jbq7938X1reKY3FO2aMtGCR1i4=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Resource = _t,
        Material = _t,
        StartDateTime = _t,
        FinishDateTime = _t,
        DurationHours = _t,
        OrderQty = _t,
        OutputHour = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Resource", type text},
      {"Material", Int64.Type},
      {"StartDateTime", type datetime},
      {"FinishDateTime", type datetime},
      {"DurationHours", type number},
      {"OrderQty", Int64.Type},
      {"OutputHour", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "QtyHour",
    each [OrderQty] / (Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24)
  ),
  #"Duplicated Column" = Table.DuplicateColumn(
    #"Added Custom",
    "StartDateTime",
    "StartDate"
  ),
  #"Duplicated Column1" = Table.DuplicateColumn(
    #"Duplicated Column",
    "FinishDateTime",
    "FinishDate"
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Duplicated Column1",
    {
      {"StartDate", type date},
      {"FinishDate", type date}
    }
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type2",
    "DaysBetween",
    each {Number.From([StartDate]) .. Number.From([FinishDate])}
  ),
  #"Expanded DaysBetween" = Table.ExpandListColumn(#"Added Custom1", "DaysBetween"),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Expanded DaysBetween",
    {{"DaysBetween", type datetime}}
  ),
  #"Added Custom3" = Table.AddColumn(
    #"Changed Type3",
    "HoursDay",
    each
      if DateTime.Date([DaysBetween])
        = [StartDate] and DateTime.Date([DaysBetween])
        = [FinishDate]
      then
        Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24
      else if DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [DaysBetween]) * 24
      else if DateTime.Date([DaysBetween]) = [StartDate] then
        Duration.TotalDays(Date.AddDays([DaysBetween], 1) - [StartDateTime]) * 24
      else
        24
  ),
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom3",
    "QtyDay",
    each [HoursDay] * [QtyHour]
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Added Custom2",
    {{"QtyDay", type number}}
  )
in
  #"Changed Type1"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
saad_mce
Frequent Visitor

Hi KNP,

Apologies for late reply. I was away for a few days. 

This works great. Thanks for the codes.

I only have two small problem.

1. Our factory is closed on Sat & Sunday. How do we avoid the calculation being perfomed on Sat & Sunday? 

2. Our factory starts on Sunday 11PM. Is there a way to consider a factory calendar in these calculation by a supporting query/table?

Regards,

Abrar

Hi Abrar,

 

No problem.

Off the top of my head, I'd be looking to use the Date.DayOfWeekName() or Date.DayOfWeek() to work out if the start or end date included the weekend and then stopping/starting the calculation at the appropriate time.

 

It may actually be easier than that. You may be able to do the day calculation after the #"Added Custom3" step and then subtract 24 from all Saturdays and 23 from all Sundays.

If you finish earlier than midnight on Friday you'd need to take this into account also.

 

I don't have any time to spend on this now so hopefully this makes sense and gets you closer to a complete solution. I'll have a look at it later in the week if I get a chance.

 

Regards,

Kim

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
Super User

Actually, I think this will get you a little closer...

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZY4xDoQwDAS/glxHZNd2LsAb+AFKSXHN/b+8hCAklGrtkTze45D9+zspQcgltTREMiqUk9pm2IBKwUjtFF5Rpz6nFtRg16DZXUp4S3U49zRK8/NK55xqLOjbinVQWuv53NJu3efdsaImuEVSyh8=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Resource = _t,
        Material = _t,
        StartDateTime = _t,
        FinishDateTime = _t,
        DurationHours = _t,
        OrderQty = _t,
        OutputHour = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Resource", type text},
      {"Material", Int64.Type},
      {"StartDateTime", type datetime},
      {"FinishDateTime", type datetime},
      {"DurationHours", type number},
      {"OrderQty", Int64.Type},
      {"OutputHour", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "QtyHour",
    each [OrderQty] / (Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24)
  ),
  #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "StartDateTime", "StartDate"),
  #"Duplicated Column1" = Table.DuplicateColumn(
    #"Duplicated Column",
    "FinishDateTime",
    "FinishDate"
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Duplicated Column1",
    {{"StartDate", type date}, {"FinishDate", type date}}
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type2",
    "DaysBetween",
    each {Number.From([StartDate]) .. Number.From([FinishDate])}
  ),
  #"Expanded DaysBetween" = Table.ExpandListColumn(#"Added Custom1", "DaysBetween"),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Expanded DaysBetween",
    {{"DaysBetween", type datetime}}
  ),
  #"Added Custom3" = Table.AddColumn(
    #"Changed Type3",
    "HoursDay",
    each
      if DateTime.Date([DaysBetween]) = [StartDate] and DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24
      else if DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [DaysBetween]) * 24
      else if DateTime.Date([DaysBetween]) = [StartDate] then
        Duration.TotalDays(Date.AddDays([DaysBetween], 1) - [StartDateTime]) * 24
      else
        24
  ),
  #"Inserted Day Name" = Table.AddColumn(
    #"Added Custom3",
    "Day Name",
    each Date.DayOfWeekName([DaysBetween]),
    type text
  ),
  #"Added Custom2" = Table.AddColumn(
    #"Inserted Day Name",
    "QtyDay",
    each
      if [Day Name] = "Saturday" then
        0 * [QtyHour]
      else if [Day Name] = "Sunday" and [HoursDay] > 1 then
        1 * [QtyHour]
      else
        [HoursDay] * [QtyHour]
  ),
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2", {{"QtyDay", type number}})
in
  #"Changed Type1"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!