Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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? 

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

4 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi Anonymous

     

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

     

    thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yep sure LivioLanzo

      Table2

      IDInteractionDateTime
      P118/09/2018 22:40
      P217/09/2018 15:20
      P31/09/2018 3:40
      P41/09/2018 22:20
      P51/09/2018 8:00
      P61/09/2018 17:30

       

      Table1

      IDShiftType
      P1Afternoon
      P2Day
      P3Afternoon
      P4Afternoon
      P5Afternoon
      P6

      Day

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        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