Calculated Measure for Time Column

[mrbuttons07]

Hello everyone,

I have a column that contains Transaction Times.

How do I create a calculated column that differentiates the time into different categories like below.

If Time is within 23:45:01.0000000 to 06:45:00.0000000, then it's Night.
If Time is within 06:45:01.0000000 to 15:15:00.0000000 then it's Morning.
If Time is within 15:15:01.0000000 to 23:45:00.0000000 then it's Noon.

Thanks!

[DatawithDinesh]

You can create a calculated column that uses switch statement to specify the period of the day depending on the condition that you specify. See below code.

TimeCategory = 
VAR CurrentTime = [TransactionTime]
RETURN 
SWITCH(
    TRUE(),
    CurrentTime >= TIME(23, 45, 1) || CurrentTime < TIME(6, 45, 0), "Night",
    CurrentTime >= TIME(6, 45, 1) && CurrentTime < TIME(15, 15, 0), "Morning",
    CurrentTime >= TIME(15, 15, 1) && CurrentTime < TIME(23, 45, 0), "Noon",
    "Unknown"
)

Hope this helps!

[rajendraongole1 · Accepted solution]

Hi @mrbuttons07 - create a below calculated column , replace your table name and time column

Time Category =

SWITCH(

    TRUE(),

    'Date'[Transaction Time] >= TIME(23, 45, 1) || 'Date'[Transaction Time] <= TIME(6, 45, 0), "Night",

    'Date'[Transaction Time] >= TIME(6, 45, 1) && 'Date'[Transaction Time] <= TIME(15, 15, 0), "Morning",

    'Date'[Transaction Time] >= TIME(15, 15, 1) && 'Date'[Transaction Time] <= TIME(23, 45, 0), "Noon",

    BLANK()

)

Hope it helps