topic Re: Calculate day of time in DAX Commands and Tips

Calculate day of time

Samiks95 — Wed, 19 Oct 2022 19:17:45 GMT

Hello World!

I have a column called Time.

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

Re: Calculate day of time

Stachu — Wed, 19 Oct 2022 19:33:16 GMT

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

Re: Calculate day of time

Samarth_18 — Thu, 20 Oct 2022 05:18:08 GMT

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

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 13:36:20 GMT

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

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 14:34:16 GMT

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

Re: Calculate day of time

Samarth_18 — Thu, 20 Oct 2022 15:20:59 GMT

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

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 16:17:22 GMT

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

Re: Calculate day of time

Samarth_18 — Thu, 20 Oct 2022 16:24:01 GMT

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

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 16:32:24 GMT

Samarth_18 Now giving few errors Sir. Some are blank.

Re: Calculate day of time

Samarth_18 — Thu, 20 Oct 2022 16:48:53 GMT

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

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 16:58:51 GMT

Samarth_18 How do I attach PBIX here Sir?

Re: Calculate day of time

Samarth_18 — Thu, 20 Oct 2022 17:01:39 GMT

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

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 17:55:13 GMT

Samarth_18

Re: Calculate day of time

Samarth_18 — Thu, 20 Oct 2022 17:32:10 GMT

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/

Re: Calculate day of time

Samiks95 — Thu, 20 Oct 2022 20:02:48 GMT

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