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)

open
open
open

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

Thanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
Thanks for your support Peter

Started to use your advice in my model.

Cheers for taking time to answer my question.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top