Calculate day of time

[Samiks95]

Hello World!

I have a column called Time. 

I want to create a column called Day of Time based on the given Time Range. 

[Samarth_18 · Accepted solution]

@Samiks95 Please try:-

Day of Time = 
SWITCH (
    TRUE (),
    'Table_'[Time] >= TIME ( 05, 00, 00 )
        && 'Table_'[Time] <= TIME ( 09, 59, 00 ), "Early Morning",
    'Table_'[Time] >= TIME ( 10, 00, 00 )
        && 'Table_'[Time] <= TIME ( 11, 59, 00 ), "Late Morning",
    'Table_'[Time] >= TIME ( 12, 00, 00 )
        && 'Table_'[Time] <= TIME ( 15, 59, 00 ), "Early Afternoon",
    'Table_'[Time] >= TIME ( 16, 00, 00 )
        && 'Table_'[Time] <= TIME ( 17, 59, 00 ), "Late Afternoon",
    'Table_'[Time] >= TIME ( 18, 00, 00 )
        && 'Table_'[Time] <= TIME ( 20, 59, 00 ), "Evening",
    'Table_'[Time] >= TIME ( 21, 00, 00 )
        && 'Table_'[Time] <= TIME ( 23, 59, 00 ) || 'Table_'[Time] >= TIME ( 00, 00, 00 )
        && 'Table_'[Time] <= TIME ( 04, 59, 00 ), "Night"
)

[Samarth_18]

Hi @Samiks95 ,

You could try to create a column as below:-

Day of Time =
SWITCH (
    TRUE (),
    'Table'[Time] >= TIME ( 05, 00, 00 )
        && 'Table'[Time] <= TIME ( 09, 59, 00 ), "Early Morning",
    'Table'[Time] >= TIME ( 10, 00, 00 )
        && 'Table'[Time] <= TIME ( 11, 59, 00 ), "Late Morning",
    'Table'[Time] >= TIME ( 12, 00, 00 )
        && 'Table'[Time] <= TIME ( 15, 59, 00 ), "Early Afternoon",
    'Table'[Time] >= TIME ( 16, 00, 00 )
        && 'Table'[Time] <= TIME ( 17, 59, 00 ), "Late Afternoon",
    'Table'[Time] >= TIME ( 18, 00, 00 )
        && 'Table'[Time] <= TIME ( 20, 59, 00 ), "Evening",
    'Table'[Time] >= TIME ( 21, 00, 00 )
        && 'Table'[Time] <= TIME ( 04, 59, 00 ), "Night"
)

Output:-

[Samiks95]

@Samarth_18 Looks like it is not displaying early morning and night value.

[Samiks95]

@Samarth_18  I am missing a lot of data not sure why. I did the exact same.

[Samarth_18]

@Samiks95 ,Please try this:-

Day of Time = 
SWITCH (
    TRUE (),
    'Table_'[Time] >= TIME ( 05, 00, 00 )
        && 'Table_'[Time] <= TIME ( 09, 59, 00 ), "Early Morning",
    'Table_'[Time] >= TIME ( 10, 00, 00 )
        && 'Table_'[Time] <= TIME ( 11, 59, 00 ), "Late Morning",
    'Table_'[Time] >= TIME ( 12, 00, 00 )
        && 'Table_'[Time] <= TIME ( 15, 59, 00 ), "Early Afternoon",
    'Table_'[Time] >= TIME ( 16, 00, 00 )
        && 'Table_'[Time] <= TIME ( 17, 59, 00 ), "Late Afternoon",
    'Table_'[Time] >= TIME ( 18, 00, 00 )
        && 'Table_'[Time] <= TIME ( 20, 59, 00 ), "Evening",
    'Table_'[Time] >= TIME ( 21, 00, 00 )
        && 'Table_'[Time] <= TIME ( 11, 59, 00 ) || 'Table_'[Time] >= TIME ( 00, 00, 00 )
        && 'Table_'[Time] <= TIME ( 04, 59, 00 ), "Night"
)

[Samiks95]

@Samarth_18 Values are still missing . How do i correct it?

[Samarth_18 · Accepted solution]

@Samiks95 Please try:-

Day of Time = 
SWITCH (
    TRUE (),
    'Table_'[Time] >= TIME ( 05, 00, 00 )
        && 'Table_'[Time] <= TIME ( 09, 59, 00 ), "Early Morning",
    'Table_'[Time] >= TIME ( 10, 00, 00 )
        && 'Table_'[Time] <= TIME ( 11, 59, 00 ), "Late Morning",
    'Table_'[Time] >= TIME ( 12, 00, 00 )
        && 'Table_'[Time] <= TIME ( 15, 59, 00 ), "Early Afternoon",
    'Table_'[Time] >= TIME ( 16, 00, 00 )
        && 'Table_'[Time] <= TIME ( 17, 59, 00 ), "Late Afternoon",
    'Table_'[Time] >= TIME ( 18, 00, 00 )
        && 'Table_'[Time] <= TIME ( 20, 59, 00 ), "Evening",
    'Table_'[Time] >= TIME ( 21, 00, 00 )
        && 'Table_'[Time] <= TIME ( 23, 59, 00 ) || 'Table_'[Time] >= TIME ( 00, 00, 00 )
        && 'Table_'[Time] <= TIME ( 04, 59, 00 ), "Night"
)

[Samiks95]

@Samarth_18  Now giving few errors Sir. Some are blank.

[Samarth_18]

@Samiks95 Can you please share your PBIX after removing sensitive data?

[Samiks95]

@Samarth_18 How do I attach PBIX here Sir?

[Samarth_18]

You can upload it in google drive share a link or you can use https://wetransfer.com/

[Samiks95]

@Samarth_18 

[Samarth_18 · Accepted solution]

@Samiks95 Seems I dont have access to it. Can you please share the file with open access?

or you can easily use https://wetransfer.com/

[Samiks95]

Sir, you're a genius. It worked. Thank you so much

[Stachu]

I'd start with creating a new table (lets call it TimeDefs) in this format:

Day Of Time	Start	End
Early Morning	05:00:00	10:00:00
Late Moring	10:00:00	12:00:00
Early Afternoon	12:00:00	16:00:00

The new column would be then:

DoT =
VAR _row_time = Table[Time] //this is the column which you want to categorize
RETURN
    MAXX (
        FILTER (
            'TimeDefs',
            'TimeDefs'[Start] >= _row_time
                && 'TimeDefs'[End] < _row_time
        ),
        [Day of Time]
    )