Help with filtering on hours between from Friday 18:00 to Monday 06:00

[lennox25]

I have this in my column which gives me the out of hours emails received for each day of the week (Mon to Sun) but I need to adapt it to only include Friday from 18:00 to Monday 06:00 - any helpers williing to help me? Thank you

Out of Hours Emails Recd 18:00 to 06:00 =

  VAR __DateTime = [Date & Time]

  VAR __Hour = HOUR('Mailbox'[Time])

  VAR __Result = IF( __Hour >= 18 || __Hour <6 || WEEKDAY(__DateTime,2) > 5,1,0)

RETURN

  __Result

[mh2587]

Out of Hours Emails Recd 18:00 to 06:00 = //try this
VAR __DateTime = [Date & Time]
VAR __Hour = HOUR('Mailbox'[Time])
VAR __Weekday = WEEKDAY(__DateTime, 2)  // 2 means Monday is the start of the week
VAR __IsWeekend = __Weekday >= 5  // Friday (5) and Saturday (6) are considered weekends
VAR __IsOutOfHours = (__Hour >= 18 || __Hour < 6)

VAR __Result = IF((__IsWeekend && __Hour >= 18) || (__IsWeekend && __Hour < 6) || (__Weekday = 1 && __Hour < 6), 1, 0)

RETURN
  __Result

[lennox25]

Hi @mh2587  thats partially worked. its not including Saturdays or Sundays though? Any ideas?

Actually what I should have explained better was from Friday 18:00 to Monday 06:00 inclusive so all time/days between Saturday and Sunday should be all hours. TIA

[mh2587]

Out of Hours Emails Recd 18:00 to 06:00 = // Try this a little modification
VAR __DateTime = [Date & Time]
VAR __Hour = HOUR('Mailbox'[Time])
VAR __Weekday = WEEKDAY(__DateTime, 2)  // 2 means Monday is the start of the week
VAR __IsWeekend = __Weekday >= 5  // Friday (5) and Saturday (6) are considered weekends
VAR __IsOutOfHours = (__Hour >= 18 || __Hour < 6)

VAR __Result = IF((__IsWeekend || __Weekday = 1) && (__Hour >= 18 || __Hour < 6), 1, 0)

RETURN
  __Result

[lennox25]

https://drive.google.com/file/d/1VEwR2ZEBoOkzSfeaFzF_xnZP1QznyDW1/view?usp=sharing 

@mh2587 please see attached its still not quite there 😞

[smpa01]

@lennox25 you can write a single measure like this

Measure = 
VAR _one =
    SUMMARIZE (
        ADDCOLUMNS (
            ADDCOLUMNS (
                SUMMARIZE (
                    FILTER (
                        ADDCOLUMNS ( ALL ( src ), "_start", INT ( CALCULATE ( MAX ( src[Date] ) ) ) ),
                        WEEKDAY ( [_start] ) = 6
                    ),
                    [_start]
                ),
                "_endDate", [_start] + 3
            ),
            "_startDatetime",
                VAR yr =
                    YEAR ( [_start] )
                VAR mo =
                    MONTH ( [_start] )
                VAR day =
                    DAY ( [_start] )
                RETURN
                    CONVERT ( yr & "-" & mo & "-" & day & " " & "18:00:00", DATETIME ),
            "_endDatetime",
                VAR yr =
                    YEAR ( [_endDate] )
                VAR mo =
                    MONTH ( [_endDate] )
                VAR day =
                    DAY ( [_endDate] )
                RETURN
                    CONVERT ( yr & "-" & mo & "-" & day & " " & "06:00:00", DATETIME )
        ),
        [_startDatetime],
        [_endDatetime]
    )
VAR _four =
    GENERATE (
        src,
        VAR ky = src[Date]
        RETURN
            FILTER ( _one, ky >= [_startDatetime] && ky <= [_endDatetime] )
    )
VAR _five =
    COUNTX ( _four, [Date] )
RETURN
    _five

PFA solution

[gmsamborn]

Hi @lennox25 

Until the end, my solution relies on calculated columns.

Date = INT( [Date & Time] )

- set data type to Date

Time = [Date & Time] - [Date]

- set data type to Time

Hour = FORMAT( [Date & Time], "yyyy-MM-dd hh:00" )

- for use on an axis

and one more...

After Hours = 
VAR _DtTm = [Date & Time]
VAR _WkDy = WEEKDAY( _DtTm, 2 )
VAR _Logic =
    IF(
        _WkDy >= 6
            || ( _WkDy = 5 && [Time] > TIME( 18, 0, 0 ) )
            || ( _WkDy = 1 && [Time] < TIME( 6, 0, 0 ) ),
        1
    )
RETURN
    _Logic

After this, I only need one measure:

Total After Hours = SUM( 'Sample Data 1'[After Hours] )

Let me know if you have any questions.

After Hours.pbix

[lennox25]

Thank you - this is perfect! I have another request for  Emails received Mon to Friday from 18:00 to 06:00.

So Monday 18:00 to Tuesday 06:00 and Tuesday 18:00 to Wednesday 06:00 etc to end Friday at 06:00am? Thank you

[gmsamborn · Accepted solution]

Hi @lennox25 

Would a calculated column like this help?

Shift = 
VAR _WkDy = WEEKDAY( [Date & Time], 2 )
VAR _Logic =
    SWITCH(
        TRUE(),
        _WkDy >= 6, "Weekend",
        _WkDy = 5 && [Time] > TIME( 18, 0, 0 ), "Weekend",
        _WkDy = 1 && [Time] < TIME( 6, 0, 0 ), "Weekend",
        [Time] > TIME( 18, 0, 0 )
            || [Time] < TIME( 6, 0, 0 ), "Night",
        "Day"
    )
RETURN
    _Logic