Dividing data into to 2 groups based on date/time manufactured to show quantity built in given shift

[CraigBlackman]

I have raised a couple of post with this problem broken down, however as of yet I have been unable to solve the riddle with all the fantastic help offered. So going to try a different approach.

1. I am using directquery
2. I have a date_dim table
3. I have a single table with a order id, wo_id, a datetime field called wo_built_datetime and a quantity build field wo_qty_built
4. The date_dim table and wo_built_datetime tables are linked via date
5. I would like to split the data into day shift and night shift based on a shift pattern. Day = 7am to 7pm, Night = 7pm - 7am
6. any items built between the hours of 12am and 7am would need to be pushed back to the day previous when displaying by date. so if built on November 28th at 3.03am it would be pushed back fall in on the 27th November
7. I would like to display that split so we can see what was done in a given shift.

I hope that makes sense and seem logical. I am very open to suggestion and maybe a better way of presenting this data.

Thanks for any help in advance.

Craig

[v-sihou-msft]

@CraigBlackman

In this scenario, you can create a calculated column to drive the time part from your datetime column.

Time Part = Time(HOUR(Table[wo_built_datetime]),MINUTE(Table[wo_built_datetime]),SECOND(Table[wo_built_datetime]))

Then you can create a shift column for tagging Day or Night.

Shift = IF(Table[Time Part]<Time(7,0,0),"Night","Day")

Now you can create a Actual Resolved Date column.

Actual Resolved Date = IF(Table[Shift]="Night",DATEADD(Table[wo_built_date],-1,DAY),Table[wo_built_date])

Regards,

[CraigBlackman]

@v-sihou-msft

Thanks for your help. I am using directquery and it tells me that the DATEADD function even with unrestricted measures enabled is unallowed.

The solution would be perferct aprt from this minor issue.

Thanks @CraigBlackman