¿Cómo encontrar el número de horas de una columna StartDate y EndDate en función de la condición?

[Anonymous]

Hola a todos,

Tengo dos tablas, Primera tabla con columnas - ID, Start_Date, End_Date

Segunda tabla con columnas - Day_of_Week, Start_Time, End_Time

END_DATE Start_Date de identificación
ABC123 01/05/2019 16:00 01/07/2019 20:00
XYZ123 01/06/2019 5:00 01/13/2019 5:00
XYZ456 01/08/2019 19:00 01/13/2019 12:00
Y

ID Day StartTime EndTime
ABC123 Sábado 13:00 18:00
XYZ123 Domingo 0:00 6:00
XYZ456 Martes 0:00 12:00

Necesito una columna Reusltant en la primera tabla que capture el número de horas dentro del Start_Date y End_Date basado en la condición de la segunda tabla. En este caso, el resultado debe ser

ID Start_Date End_Date Timeline_Hours
ABC123 01/05/2019 16:00 01/07/2019 20:00 2
XYZ123 01/06/2019 5:00 01/13/2019 5:00 6
XYZ456 01/08/2019 19:00 01/13/2019 12:00 0

Para el primer registro: ABC123 - Número de horas con el Start_Date y End_date en función de la condición es de 2 Horas.

Motivo - Fecha comienza desde Staurday 16:00 (4PM) y termina el lunes 20:00 (8PM),

La condición en la segunda tabla dice sábado de 13:00 a 18:00 por lo que la superposición es de 2 horas (de 16:00 a 18:00)

Del mismo modo, el segundo tiene una duración de más de una semana y la superposición para la primera semana es de 1 hora (de 5:00 a 6:00) y para la segunda semana es de 5 horas (de 0:00 a 5:00)

Para el tercero no se superponen por lo que 0 hora.

¿Es posible hacerlo en DAX o Power Query? ¿Cómo puedo hacer esto?

Gracias

Nagaraj

[v-gizhi-msft · Accepted solution]

Hola

¿Por qué el 'ABC123' no dura más de una semana?

Creo que según su lógica, el 2019/1/5 es el primer día de la primera semana en este caso.

Si es así, siga estos pasos:

1)Crear dos columnas:

Start_WeekNo = WEEKNUM('Table 1'[Start_Date]-4)

End_WeekNo = WEEKNUM('Table 1'[End_Date]-4)

2)Pruebe esta columna:

Timeline_Hours = 
VAR a =
    TIME ( HOUR ( 'Table 1'[Start_Date] ), MINUTE ( 'Table 1'[Start_Date] ), SECOND ( 'Table 1'[Start_Date] ) )
VAR b =
    TIME ( HOUR ( 'Table 1'[End_Date] ), MINUTE ( 'Table 1'[End_Date] ), SECOND ( 'Table 1'[End_Date] ) )
VAR check_a =
    SWITCH (
        TRUE,
        a < RELATED ( 'Table 2'[EndTime] )
            && a > RELATED ( 'Table 2'[StartTime] ), DATEDIFF ( a, RELATED ( 'Table 2'[EndTime] ), HOUR ),
        a <= RELATED ( 'Table 2'[StartTime] ), DATEDIFF (
            RELATED ( 'Table 2'[StartTime] ),
            RELATED ( 'Table 2'[EndTime] ),
            HOUR
        ),
        a >= RELATED ( 'Table 2'[EndTime] ), 0
    )
VAR check_b =
    SWITCH (
        TRUE,
        b < RELATED ( 'Table 2'[EndTime] )
            && b > RELATED ( 'Table 2'[StartTime] ), DATEDIFF ( RELATED ( 'Table 2'[StartTime] ), b, HOUR ) + check_a,
        b <= RELATED ( 'Table 2'[StartTime] ), DATEDIFF (
            RELATED ( 'Table 2'[StartTime] ),
            RELATED ( 'Table 2'[EndTime] ),
            HOUR
        ),
        b >= RELATED ( 'Table 2'[EndTime] ), 0
    )
RETURN
    IF ( 'Table 1'[Start_WeekNo] = 'Table 1'[End_WeekNo], check_a, check_b )

3)El resultado muestra:

Vea mi archivo pbix adjunto.

Saludos

Giotto

[amitchandak]

@Nagaraja

@Nagaraja , ¿Una tienda abre solo en un día o más que un día, si se abre solo en un día

Podemos traer tiempo de la mesa 2 tabla1

Hora de inicio Std á minx(Table2, Table1[ID] ?Table2[ID]),[Hora de inicio])
Inicio Fin Std á minx(Table2, Table1[ID] ?Table2[ID]),[END Time])

Además, obtenga de la fecha y hora

Hora de inicio: Tabla[Start_Date].time

Hora de finalización: Tabla[End_Date].tiempo

A continuación, podemos tomar datediff(max([Start Time],[Start time Std]),Min([End Time],[End time Std]),hour)

Y ahora tenemos multiplicar basado en la lógica no de días

[Anonymous]

@amitchandak Lo siento no entendí la lógica de su solución, ¿cómo voy a obtener el número de horas que estaciona la codición en la segunda tabla usign ese método.

¡Necesito obtener la cantidad de tiempo que satisface la condición en la segunda tabla del período de la hora de inicio y finalización!

Gracias

[amitchandak]

@Nagaraja , busque el archivo en:https://www.dropbox.com/s/xcrt4cw50vt6awp/betweentimearossdate.pbix?dl=0

Pocos cálculos de tiempo necesitan ser reelaborados, ya que no obtuve la misma diferencia de tiempo

[amitchandak]

@Nagaraja , ¿Una tienda abre solo en un día o más que un día, si se abre solo en un día

Podemos traer tiempo de la mesa 2 tabla1

Hora de inicio Std á minx(Table2, Table1[ID] ?Table2[ID]),[Hora de inicio])
Inicio Fin Std á minx(Table2, Table1[ID] ?Table2[ID]),[END Time])

Además, obtenga de la fecha y hora

Hora de inicio: Tabla[Start_Date].time

Hora de finalización: Tabla[End_Date].tiempo

A continuación, podemos tomar datediff(max([Start Time],[Start time Std]),Min([End Time],[End time Std]),hour)

Y ahora tenemos multiplicar basado en la lógica no de días