Calculating shifts

Many organizations have the concept of shifts. Essentially, a 24-hour day is typically split up into 3 equal shifts of 8 hours each. It is often important for organizations to understand during what shift an event occurred. This recipe uses DAX to calculate the correct shift for each hour of the day.

Getting ready

To prepare for this recipe, do the following:

1. Create a table called R06_Table using the following formula:

R06_Table = 
    VAR __Hours = 
            SELECTCOLUMNS(
                GENERATESERIES(1 , 12 , 1),
                "Hour",
                [Value] & ":00:00 "
            )
    VAR __Period = 
        SELECTCOLUMNS(
            { ("AM"),("PM") },
  ...