Forum Discussion

lornafnb's avatar
lornafnb
Helper I
3 years ago
Solved

Create conditional field based on hour

Hi everyone,   I have a timestamp field called datetimequeued - (eg .. 11/15/2021 9:34:37 AM)   I need to create a filter, based on this field. The following conditions should be checked...
Need Help
  • Kishore_KVN's avatar
    Kishore_KVN
    3 years ago

    Hello lornafnb if you just want a "Yes", "No" column, you can use below calculation: 

    Check = 
SWITCH
(
    TRUE(),
        FORMAT('Table'[Date],"DDD") = "Mon" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Tue" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Wed" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Thu" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Fri" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Sat" && HOUR('Table'[Date]) >= 14, "Yes",
        FORMAT('Table'[Date],"DDD") = "Sun" && HOUR('Table'[Date]) >= 14, "Yes",
"No"
)

    Output looks as below:

     

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

     

lornafnb's avatar
lornafnb
Helper I
3 years ago

Thank you Kishore, this is a bit advanced for me as I've never worked with variables within PowerBI before.

I basically just want to create one field (lets call it 'Afterhours_Flag') that contains 'Yes' or 'No', using the criteria I mentioned (in terms of weekdays and Hours).

Kishore_KVN's avatar
Kishore_KVN
Solution Sage
3 years ago

Hello lornafnb if you just want a "Yes", "No" column, you can use below calculation: 

Check = 
SWITCH
(
    TRUE(),
        FORMAT('Table'[Date],"DDD") = "Mon" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Tue" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Wed" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Thu" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Fri" && HOUR('Table'[Date]) >= 19, "Yes",
        FORMAT('Table'[Date],"DDD") = "Sat" && HOUR('Table'[Date]) >= 14, "Yes",
        FORMAT('Table'[Date],"DDD") = "Sun" && HOUR('Table'[Date]) >= 14, "Yes",
"No"
)

Output looks as below:

 

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