Create conditional field based on hour

[lornafnb]

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

[Kishore_KVN]

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]

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 · Accepted solution]

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