Help with creating a 24 hour Work Shift date table

[aggiemullins]

I'm a DAX noob trying to create a calculated Work Shift date table to return a work shift letter based on date and time.

We have 4 shifts (A, B, C, D) and they run for 24 hours per shift, 7 days a week. The shift changes everyday at 7AM. 

For example: from

12/31/20 07:00 am - 01/01/20 07:00 am = B Shift

01/01/20 07:00 am - 01/02/20 07:00 am = C Shift

01/02/20 07:00 am - 01/03/20 07:00 am = D Shift

01/03/20 07:00 am - 01/04/20 07:00 am = A Shift

Thanks!

[HotChilli]

Have you got the dates in a table already or are you starting from scratch?

If I was doing this in Power Queryand starting from scratch,  I would create a list of dates

{Number.From(#date(2019, 3, 20))..Number.From(#date(2020, 3, 20))}

Convert to table. Add a new column which is Column1 + 1.

Change type to datetime.

This gives a 2 column table of the correct dates.  Add 07:00 to both columns .

Add an Index from 0

Add a column using Number.Mod(Index,4) <- This gives 0,1,2,3 repeating down the table

Add a column with an if..then..else to allocate 0,1,2,3 -> A,B,C,D (depending on where the cycle starts)

Good luck

[aggiemullins]

Thank you! It took me a bit, but I was able to recreate the table and then I've been stuck on how to link it to the main table with the transactions ever since. I'm fairly certain it's an if statement, but the formula is stumping me between the two tables.

I apologize as I should have been a little more explicit on my need. You answered exactly what I asked for, but I think there may be a better way to do what I'm trying to achieve. If proper etiquette dictates I mark this as solved and start a new post, please let me know!

I've got a main table with a list of transactions that contains a date/time column of when the transactions occurred.

My goal is to add a calculated(?) column that will automatically add the Shift letter that was on duty at the time the transaction occurred. Either based on the date table you gave me or maybe by a custom calendar table with a date/time shift formula?

I can create the date/shift table from your instructions. If this is best option, can you help me with the correct formula on linking it to the transaction date/time column? Or, if there's an easier, more efficient way to achieve my goal, feel free to let me know.

Thanks again.

[HotChilli · Accepted solution]

It'll be something like this:

ColumnK = VAR _date = TableTxn[date]
RETURN
    CALCULATE(MAX(TableShift[shift]), FILTER(TableShift, _date >= TableShift[start] && TableShift[end] > _date ))

 Please adapt and test as required.