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

djspod

New Member
Joined
Dec 11, 2016
Messages
8
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
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
 

djspod

New Member
Joined
Dec 11, 2016
Messages
8
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.[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

https://www.excelforum.com/excel-ch...etween-17-00-04-00-shift-dax.html#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

[/FONT]
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
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
 

djspod

New Member
Joined
Dec 11, 2016
Messages
8
Thanks for your support Peter

Started to use your advice in my model.

Cheers for taking time to answer my question.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,322
Messages
5,449,712
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top