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 tim...
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.

Anonymous's avatar
Anonymous
Not applicable
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.