cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Adición de horas de trabajo a la hora actual (considerando horas de trabajo y días festivos)

Estoy tratando de agregar una columna con el número variable de horas a la hora actual. Me gustaría considerar las horas de trabajo, los días de trabajo y las vacaciones en este cálculo.

Horario de trabajo: 7AM-12AM

Días laborables: De lunes a sábado

Vacaciones: (ver más abajo)

HolidayList

01/01/20
05/29/20
07/03/20
09/07/20
11/26/20
12/25/20

Ejemplos:

CurrentTimeWorkHoursCurrentTime + WorkHoursNotas
07/01/20 01:00PM1207/02/20 08:00AMEl día de trabajo es de 7:00AM-12:00AM. Así que +12H es al día siguiente a las 8:00AM.
07/01/20 04:00AM307/01/20 10:00AM04:00AM es antes de que comience el día de trabajo (7:00AM). Así que +3H de 07/01/2018 7:00AM es 07/01/2018 10:00AM
07/11/20 08:00PM607/13/20 09:00AM07/12/2020 es un viernes, por lo que debe saltarse el domingo hasta la fecha del lunes 07/13/2020. +6H termina a las 07/13/20 09:00AM.
07/03/20 09:00PM707/06/20 11:00AM07/04/20 es un día festivo y 07/05/2020 es un domingo, por lo que tenemos que saltar al lunes 07/06/2020. +7H termina a las 07/06/20 11:00AM.

En mi tabla todas las horas para CurrentTime serán las mismas, pero sólo dio algunos ejemplos para mostrar la lógica.

Cualquier ayuda es apreciada!

4 REPLIES 4
Highlighted
Super User I
Super User I

Hola @coffeecup

Hay un muy buen artículo aquí sobre este cálculo exacto:

https://www.sqlbi.com/blog/alberto/2019/03/25/using-dax-with-datetime-values/

En el artículo se describe cómo configurar la tabla Date (con un indicador y un contador para los días laborables) y el propio cálculo.

Por favor, vuelva a publicar si necesita más ayuda.

saludos

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Community Support
Community Support

hola @coffeecup

En primer lugar, debe definir una tabla de calendario y una tabla de vacaciones.

A continuación, cree una columna de cálculo como se indica a continuación:

Result = 
    VAR StartingDateTime = [CurrentTime]
    VAR TimeToAdd = TIME ( [WorkHours], 00, 0 )
     
    --
    --    These are parameters: working time is 09:00 - 17:00
    --
    VAR WorkTimeStart = TIME ( 07, 00, 00 )
    VAR WorkTimeEnd = TIME ( 23, 59, 00 ) + TIME ( 00, 01, 00 )
    VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )
     
    --
    --    Split StartingDateTime in two parts: date and time
    --
    VAR StartingTime = StartingDateTime - TRUNC ( StartingDateTime )
    VAR StartingDate = StartingDateTime - StartingTime
     
    --
    --    Check how many hours in the first day, how many hours in the last
    --  day and how many working days are needed in between
    --
    VAR FirstDayHours =
        MIN (
            MIN ( WorkTimeEnd - StartingTime, WorkingHours ),
            TimeToAdd 
        )
    VAR RemainingHours = TimeToAdd - FirstDayHours
    VAR RemainingDays = QUOTIENT ( RemainingHours, WorkingHours )
    VAR LastDayHours = RemainingHours - ( RemainingDays * WorkingHours )
     
    --
    --  Here we compute the end date, by taking the current working day
    --  number and searching for the date which happens to be on the 
    --  starting working day number + the number of working days
    --
    VAR StartingDayNumber =
        LOOKUPVALUE (
            'Date'[WorkingDayNumber],
            'Date'[Date], StartingDate 
        )
    VAR LastWorkingDayNumber =
        StartingDayNumber + RemainingDays 
        + 1 * ( LastDayHours > 0 )
    VAR LastDay =
        LOOKUPVALUE (
            'Date'[Date],
            'Date'[WorkingDayNumber], LastWorkingDayNumber
        )
     
    --
    --    Here we go, this is the result
    --
    VAR Result =IF(RemainingHours =0, LastDay + WorkTimeStart + FirstDayHours,
 LastDay + WorkTimeStart + LastDayHours)

RETURN
Result

Resultado:

11.JPG

y aquí está el archivo pbix de muestra, por favor pruébelo.

saludos

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

@v-lili6-msft @OwenAuger

Gracias por sus respuestas, ¿tiene alguna solución sobre cómo manejar los casos donde la duración del tiempo es superior a 24H? En este momento, mis valores se están convirtiendo en tiempo, así que si intentara agregar +36H, en su lugar solo agregaría +12H.

Highlighted

hola @coffeecup

Usted puede tratar de ajustarlo como esto:

Result = 
    VAR StartingDateTime = [CurrentTime]
    VAR TimeToAdd = TIME ( [WorkHours], 00, 0 ) + + INT([WorkHours]/24)
     
    --
    --    These are parameters: working time is 09:00 - 17:00
    --
    VAR WorkTimeStart = TIME ( 07, 00, 00 )
    VAR WorkTimeEnd = TIME ( 23, 59, 00 ) + TIME ( 00, 01, 00 )
    VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )
     
    --
    --    Split StartingDateTime in two parts: date and time
    --
    VAR StartingTime = StartingDateTime - TRUNC ( StartingDateTime )
    VAR StartingDate = StartingDateTime - StartingTime
     
    --
    --    Check how many hours in the first day, how many hours in the last
    --  day and how many working days are needed in between
    --
    VAR FirstDayHours =
        MIN (
            MIN ( WorkTimeEnd - StartingTime, WorkingHours ),
            TimeToAdd 
        )
    VAR RemainingHours = TimeToAdd - FirstDayHours
    VAR RemainingDays = QUOTIENT ( RemainingHours, WorkingHours )
    VAR LastDayHours = RemainingHours - ( RemainingDays * WorkingHours )
     
    --
    --  Here we compute the end date, by taking the current working day
    --  number and searching for the date which happens to be on the 
    --  starting working day number + the number of working days
    --
    VAR StartingDayNumber =
        LOOKUPVALUE (
            'Date'[WorkingDayNumber],
            'Date'[Date], StartingDate 
        )
    VAR LastWorkingDayNumber =
        StartingDayNumber + RemainingDays 
        + 1 * ( LastDayHours > 0 )
    VAR LastDay =
        LOOKUPVALUE (
            'Date'[Date],
            'Date'[WorkingDayNumber], LastWorkingDayNumber
        )
     
    --
    --    Here we go, this is the result
    --
    VAR Result =IF(RemainingHours =0, LastDay + WorkTimeStart + FirstDayHours,
 LastDay + WorkTimeStart + LastDayHours)

RETURN
Result

saludos

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors