Dax Help - Column to determine if date time within shift hours

[Anonymous]

Hi there

I have a fact table with a date time field and dimension table with the shift type.

I need to calculate, based on the shift being undertaken, whether an interaction occured within the shift hours or outside the shift hours. 

The shift start and end times are columns in any table.

Table1(ShiftType) = "Afternoon"

Shift Times are 12:30pm to 11:30pm

Table1(ShiftType) = "Day"

Shift times are 08:30am to 16:30pm

I'm trying to calculate whether Table2(InteractionDateTime) has occured outside those hours for the relevant shift. 

Any advice? 

[LivioLanzo]

Hi @Anonymous

would you be able to past a sample of your two tables?

thank you

[Anonymous]

Yep sure @LivioLanzo

Table2

ID	InteractionDateTime
P1	18/09/2018 22:40
P2	17/09/2018 15:20
P3	1/09/2018 3:40
P4	1/09/2018 22:20
P5	1/09/2018 8:00
P6	1/09/2018 17:30

Table1

ID	ShiftType
P1	Afternoon
P2	Day
P3	Afternoon
P4	Afternoon
P5	Afternoon
P6	Day

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Please follow the steps below.

1. Create two calculated columns in Table 1.

starttime = IF('Table1'[ShiftType]="Afternoon",TIME(12,30,00),IF('Table1'[ShiftType]="Day",TIME(8,30,00)))

endtime = IF('Table1'[ShiftType]="Afternoon",TIME(23,30,00),IF('Table1'[ShiftType]="Day",TIME(16,30,00)))

2. Create the calculated column in Table 2.

Column 2 =
IF (
    AND (
        TIMEVALUE ( 'Table2'[InteractionDateTime] )
            >= TIMEVALUE ( RELATED ( Table1[starttime] ) ),
        TIMEVALUE ( 'Table2'[InteractionDateTime] )
            <= TIMEVALUE ( RELATED ( 'Table1'[endtime] ) )
    ),
    1,
    0
)

Here is the output.

More details, you could refer to the attacement below.

If you still need help, could you share your desired output so that we could help further on it?

Best Regards,

Cherry

[Anonymous]

Worked really well. Thanks heaps!