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

[djspod]

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
​

 

[peter789]

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]

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]

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]

Thanks for your support Peter

Started to use your advice in my model.

Cheers for taking time to answer my question.