Results 1 to 5 of 5

Thread: Calculate Units Picked 17:00 - 04:00am (Shift)

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate Units Picked 17:00 - 04:00am (Shift)

    Hi,

    I have created a Data Model from a block of data.

    The warehouse guys work from 17:00 to 04:00 daily, what's the best approach to obtain the units picked but displayed in one pivot table?

    I can achieve this over 2 pivot tables using filters but ideally I want this in one table.

    Each shift spans over 2 dates, 12am to 4am is the next date as they work past midnight (Sales Date Order)

    Ideally I need a Dax Calculation column of a Dax measure to breakdown the Total Units, to filter 17:00-00:00 of one date and
    Total Units, to filter 00:00 - 04:00am of the next date.

    They can also work the following on the same shift at 17:00 so the pivot will pick up that unit data too if not filtered.

    I'm after units picked per hour per shift, not spread over 2 shifts.

    Can anyone help please? (I'm new to the Dax data model world)


    I have attached links to jpg's of my data model.

    Thanks

  2. #2
    Board Regular
    Join Date
    Nov 2016
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Units Picked 17:00 - 04:00am (Shift)

    I don't really understand your full requirements but how about adding a helper column in the Data Model to subtract 17 hours from the actual date time? Then create a column just containing the shift date to either filter or group the data in the Pivot.
    Peter

  3. #3
    New Member
    Join Date
    Dec 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Units Picked 17:00 - 04:00am (Shift)

    Hi Peter,

    Thanks for taking time to answer.
    I struggled to add my photo screenshots on here, so added the post to the Excel Forum too.

    You will get a better understanding from the photos.

    https://www.excelforum.com/excel-cha...ml#post5123004

    Think what you were saying, helper column, I guess I could do something like that with the Power Query on the Fact Table.
    Not sure what you mean by subtracting 17 hours though, why do you suggest 17 hours?

    An example of the Warehouse Process would be as folows:

    Start shift at 17:00, work till 05:00, finishing picking around 3 to 4am the following morning (Different date - IE Sales Picking date)

    Pick several products per hour of many units to each loading position for each product.

    Hope that helps explain better.

    Thanks

    Darren


  4. #4
    Board Regular
    Join Date
    Nov 2016
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Units Picked 17:00 - 04:00am (Shift)

    Darren,
    Unfortunately although I can see your screen shots they make little sense as I cannot see what your input data looks like so I'm guessing what you are trying to achieve.
    Why subtract 17 hours? Because 17:00 is when the shift starts. That artificially moves the start of the shift back to midnight. By just taking the date from the back shifted date and time you can create a "Shift Date" which enables grouping or filtering and includes the actual dates and times which straddle the real midnight.

    Peter

  5. #5
    New Member
    Join Date
    Dec 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Units Picked 17:00 - 04:00am (Shift)

    Thanks for your support Peter

    Started to use your advice in my model.

    Cheers for taking time to answer my question.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •