Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Samiks95
Regular Visitor

Calculate day of time

Hello World!

I have a column called Time. time.PNG

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

2 ACCEPTED SOLUTIONS

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

@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.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

14 REPLIES 14
Samarth_18
Community Champion
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:-

Samarth_18_0-1666243076681.png

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

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

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

@Samarth_18 How do I attach PBIX here Sir?

You can upload it in google drive share a link or you can 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.
Appreciate your Kudos!!
Connect on Linkedin

@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.
Appreciate your Kudos!!
Connect on Linkedin

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

Stachu
Community Champion
Community Champion

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

Day Of TimeStartEnd
Early Morning05:00:0010:00:00
Late Moring10:00:0012:00:00
Early Afternoon12:00:0016: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]
    )

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.