cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Calculate day of time

Hello World!

I have a column called Time.

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

2 ACCEPTED SOLUTIONS
Community Champion

``````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"
)``````

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Community Champion

@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/

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

14 REPLIES 14
Community Champion

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:-

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

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

Regular Visitor

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

Community Champion

``````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"
)``````

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

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

Community Champion

``````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"
)``````

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

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

Community Champion

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

@Samarth_18 How do I attach PBIX here Sir?

Community Champion

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor
Community Champion

@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/

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

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

Community Champion

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]
)``````