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

1. If the day (datetimequeued) is between Mon-Friday - then identify all records where hour(datetimequeued) >= 7 pm

2. Else - if the day (datetimequeued) is either Saturday or Sunday, identify where hour(datetimequeued) >= 2 pm.

 

I'm a beginner so please if it can be simple :).

Thank you

 

 

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

     

3 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    3 years ago

    Hello lornafnb , 

    You have to create two columns and a measure to achieve this. 

    1. Create two columns, where one column is only date and another column will pull out hours from the main column. So the results will look as below:

     

    2. Create your measure using below calculation:

    Calculate Count of Days = 
Var Day_Selected = FORMAT(SELECTEDVALUE('Table'[Only Date]),"DDD")
Var Mon_Fri = CALCULATE(COUNT('Table'[Date]),FILTER('Table','Table'[Hour_Selected] >= 19))
Var Sat_Sun = CALCULATE(COUNT('Table'[Date]),FILTER('Table','Table'[Hour_Selected] >= 14))
Var Result = 
        SWITCH(TRUE(),
        Day_Selected = "Mon", Mon_Fri,
        Day_Selected = "Tue", Mon_Fri,
        Day_Selected = "Wed", Mon_Fri,
        Day_Selected = "Thu", Mon_Fri,
        Day_Selected = "Fri", Mon_Fri,
        Day_Selected = "Sat", Sat_Sun,
        Day_Selected = "Sun", Sat_Sun,
        BLANK())
Return
Result

    In the slicer pull only date column and in the card visual drag your measure. 

    Output looks like this:

    As 20 November 2021 is sunday its calculating morethan 2PM. Mon-Fri it will calculate morethan 7PM.

     

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