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

     

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