Group data per day that runs from 6AM- next day 5:59AM

[Philip3210]

Hi All,

I need to group data per day, similar to WEEKDAY function in Excel or creating a "day" column based on the date in the query editor, but my "day" runs from 6-6AM, so basing the day on a date only doesn't work. We work a three shift model with the first shift starting at 6AM, so everything from midnight to 6AM should be included in the figure for the previous day.

So, Monday 6AM-Tuesday 5:59AM should be categorised as Monday, for example, and Tuesday 6AM-Wednesday 5:59AM should be Tuesday - I'm a bit at a loss here. Hoping someone has a simple solution.

Hope this illustrates my request better:

Malfunct. start / Start Malfn (T)/ WEEKDAY= / DESIRED OUTPUT
2018/01/29 / 05:59:59 / 1 / 7
2018/01/29 / 06:47:00 / 1 / 1
2018/01/30 / 03:40:00 / 2 / 1
2018/01/30 / 03:57:53 / 2 / 1

 

[gazpage]

Not sure if you are doing this in Power Query or DAX, but regardless it seems like a pretty simple conditional column. A simple IF() to check if the time is <6am and if it is subtract 1 from the weekday. Wrap the whole thing in MOD to deal with the 0 that would occur in your first row.