Calculate the shift# from a date time table

DAX DAX Calculations
1

How to add the shift number to a date time column from a table in power BI?
The condition is
For weeknumber = even
“shift 3” from 06:00 to 14:00
“shift 1” from 14:00 to 22:00
“shift 5” from 22:00 to 06:00 !? next day

For weeknumber <> even

“shift 1” from 06:00 to 14:00
date time column.xlsx (181.0 KB)

“shift 3” from 14:00 to 22:00
“shift 5” from 22:00 to 06:00 !? next day
again many thanks in advance
Roger

2

Hi @Roger,

Last shift in Even and Odd weeks is “Shift 5” so that shouldn’t be a problem.
Created this custom M function for you, which requires a datetime value and a start of weekday number (0 = sunday … 6 = saturday) if omitted it will default to Monday as start of weekday.

(DateTime as datetime, StartOfWeek as number) =>
  let
    nDate = Date.From(DateTime), 
    nTime = Time.From(DateTime), 
    nSOWD = if List.Contains( {0..6}, StartOfWeek ) then StartOfWeek else 1, 
    Result = 
      if nTime >= #time(6, 0, 0) and nTime < #time(14, 0, 0)
      then
        (
          if Number.IsEven(Date.WeekOfYear(Date.StartOfWeek(nDate, nSOWD))) then
            "Shift 3"
          else
            "Shift 1"
        )
      else if nTime >= #time(14, 0, 0) and nTime < #time(22, 0, 0)
      then
        (
          if Number.IsEven(Date.WeekOfYear(Date.StartOfWeek(nDate, nSOWD))) then
            "Shift 1"
          else
            "Shift 3"
        )
      else
        "Shift 5"
  in Result

.
I called this function fxShifts and invoked it over your sample like so

image
image707×381 15.4 KB

Here’s your sample.
eDNA - Calculate 3 Shifts in Even and Odd Weeks.pbix (16.5 KB)

I hope this is helpful

4 Likes
3

Melissa, many thanks again for the solution, looks very complicated to make .
How do I use this function? When I open the Pbix file it’s empty.
Can I use this function in other pbix files?

do I paste the code in a blanc query like the
Date Table code.txt for making a calendar table?

kind regards,
Roger

4

Hi @Roger,

Please download the file again from my previous post.

image
image949×385 18.7 KB

Go to the Query Editor by pressing the “Transform Data” button on the Home tab of the ribbon.
First press the “Manage Parameter” button and add the full filepath to the list of values, select your location - the summarize query will then restore.

I hope this is helpful

5

Melissa,
Got it finally to work.
last question:
For use in other PBIX files I can simply copy the code from the fxshifts function and paste it in the advanced editor?

Again many thanks for this great solution

Roger

6

Hi @Roger,

Yes you can transfer this custom fxShifts function to any other file, just like you would with a power query Date table function - that’s the added bonus of turning it into a custom function :wink: