Change Date Time to Shift Identifier

[IvoA93]

I have a report that i have a date time column. I want to add another column to the data that looks at this field and then does the following

• Has the value as one of two shift times.
• The shift times start at 7am & 7pm
• The 7am shift needs to have a value of the Date from the date time and then the word Day so for example 13/11/24 Day
• The 7pm shift needs to have a value of the Date from the date time and then the word Night so for example 13/11/24 Night
• The night shift also needs to include anything before 7am the following day.

Hopefully i have made sense here. Please can someone advise on the best way to do this.

[Anonymous · Accepted solution]

Hi, @IvoA93 

Based on your information, I create a sample table:

Then create a calculated column, and try the following DAX expression:

ShiftIdentifier = 
VAR ShiftTime = TIME(HOUR([DateTime]), MINUTE([DateTime]), SECOND([DateTime]))
VAR ShiftDate = FORMAT([DateTime], "MM/dd/yyyy")
RETURN 
IF(
    ShiftTime >= TIME(7, 0, 0) && ShiftTime < TIME(19, 0, 0),
    ShiftDate & " Day",
    IF(
        ShiftTime >= TIME(19, 0, 0) || ShiftTime < TIME(7, 0, 0),
        ShiftDate & " Night",
        BLANK()
    )
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @IvoA93 ,

your case is very similar to this one:

https://community.fabric.microsoft.com/t5/Desktop/Help-Insert-Shift-into-Data-Table-Based-On-Time-Pa...

[AnalyticPulse]

Try adding this calculated column in you table:

Shift =
VAR CurrentDateTime = 'Table'[DateTimeColumn]
VAR ShiftDate = DATE(YEAR(CurrentDateTime), MONTH(CurrentDateTime), DAY(CurrentDateTime))
VAR ShiftStartDay = ShiftDate + TIME(7, 0, 0)
VAR ShiftStartNight = ShiftDate + TIME(19, 0, 0)
VAR NextDayShiftStart = ShiftDate + 1 + TIME(7, 0, 0)

RETURN
IF(
CurrentDateTime >= ShiftStartDay && CurrentDateTime < ShiftStartNight,
FORMAT(ShiftDate, "dd/MM/yy") & " Day",
FORMAT(ShiftDate, "dd/MM/yy") & " Night"
)
https://analyticpulse.blogspot.com/
https://analyticpulse.blogspot.com/p/introduction.html
https://www.youtube.com/@AnalyticPulse