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.

AnalyticPulse's avatar
AnalyticPulse
Icon for Solution Sage rankSolution Sage
1 year ago

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