Forum Discussion

IvoA93's avatar
IvoA93
New Member
1 year ago
Solved

Change Date Time to Shift Identifier

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.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies