Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Dharani_98
Frequent Visitor

Hora y fecha en Power BI Desktop

Quería crear una columna calculada que devuelva el totalde horas de trabajo en función de la fecha de inicio. El resultado de mi cálculo

columna debe ser [9,4:30,13].

es decir, B trabajó durante 9 horas el 6-4-2020

4:30 horas el 7-4-2020

13 horas el 8-4-2020.

Otra columna calculada para el salario Si trabaja de 8 am a 5pm cada hora pagamos un salario diferente (digamos 100 por hora).

Del mismo modo, mientras se trabaja de 5.01 pm a 7:59 am se debe pagar una cantidad diferente por cada hora (digamos 200 por hora).

Por lo tanto, el resultado de esta columna calculada debe ser

en 6-4-2020 es 1050 (de 9:30 am a 5:00 pm es 750 y de 5:00pm a 6:30 pm es 300)

en 7-4-2020 es 900 (de 7:30 pm a 12:00 am es 900)

en 8-4-2020 es 1850 (de 12:00 am a 4:00 am es 800 y de nuevo de 9:30 am a 6:30 pm es 1050)

EmpleadoStartDate y HoraFecha y hora de finalización
b06-04-2020 09:30:0006-04-2020 18:30:00
b07-04-2020 19:30:0008-04-2020 04:00:00
b08-04-2020 09:30:0008-04-2020 18:30:00

amablemente me ayudan con el cálculo de dax para las dos columnas calculadas requeridas.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hola @Dharani_98 ,

Yo creo una medida, no una columna calculada. Por favor, compruebe:

1. Cree la columna [StartDate].

StartDate = DATEVALUE('Table'[StartDate and Time])

startdate.PNG

2. Cree una tabla Calendario.

Calendar = 
CALENDAR (
    MINX ( 'Table', DATEVALUE ( 'Table'[StartDate and Time] ) ),
    MAXX ( 'Table', DATEVALUE ( 'Table'[End Date And Time] ) )
)

calendar.PNG

3. Crear relación.

relationships.jpg

4. Cree la medida [Rank_].

Rank_ =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[StartDate] ) ),
    ,
    ASC,
    DENSE
)

5. Crear [Medida de salario].

Salary Measure = 
VAR LastRank = [Rank_] - 1
VAR LastStartDateTime =
    CALCULATE (
        MAX ( 'Table'[StartDate and Time] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Rank_] = LastRank )
    )
VAR LastEndDateTime =
    CALCULATE (
        MAX ( 'Table'[End Date And Time] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Rank_] = LastRank )
    )
VAR LastDateDiff =
    DATEDIFF ( LastStartDateTime, LastEndDateTime, DAY )
VAR ThisDateDiff =
    DATEDIFF (
        MAX ( 'Table'[StartDate and Time] ),
        MAX ( 'Table'[End Date And Time] ),
        DAY
    )
VAR StartDateTime =
    IF (
        LastDateDiff = 1,
        CONVERT (
            DATEVALUE ( MAX ( 'Table'[StartDate and Time] ) ) & " "
                & TIME ( 0, 0, 0 ),
            DATETIME
        ),
        MAX ( 'Table'[StartDate and Time] )
    )
VAR EndDateTime =
    IF (
        ThisDateDiff = 1,
        CONVERT (
            DATEVALUE ( MAX ( 'Table'[End Date And Time] ) ) & " "
                & TIME ( 0, 0, 0 ),
            DATETIME
        ),
        MAX ( 'Table'[End Date And Time] )
    )
VAR SpecifiedStartTime =
    CONVERT (
        DATEVALUE ( MAX ( 'Table'[StartDate and Time] ) ) & " "
            & TIME ( 8, 0, 0 ),
        DATETIME
    )
VAR SpecifiedEndTime =
    CONVERT (
        DATEVALUE ( MAX ( 'Table'[StartDate and Time] ) ) & " "
            & TIME ( 17, 0, 0 ),
        DATETIME
    )
VAR Result =
    IF (
        LastDateDiff = 1,
        IF (
            EndDateTime > SpecifiedStartTime
                && EndDateTime <= SpecifiedEndTime,
            DATEDIFF ( StartDateTime, LastEndDateTime, MINUTE ) / 60 * 200
                + DATEDIFF ( MAX ( 'Table'[StartDate and Time] ), EndDateTime, MINUTE ) / 60 * 100,
            IF (
                EndDateTime > SpecifiedEndTime,
                DATEDIFF ( StartDateTime, LastEndDateTime, MINUTE ) / 60 * 200
                    + DATEDIFF ( MAX ( 'Table'[StartDate and Time] ), SpecifiedEndTime, MINUTE ) / 60 * 100
                    + DATEDIFF ( SpecifiedEndTime, EndDateTime, MINUTE ) / 60 * 200
            )
        ),
        IF (
            LastDateDiff <> 1,
            IF (
                StartDateTime >= SpecifiedStartTime
                    && EndDateTime <= SpecifiedEndTime,
                DATEDIFF ( StartDateTime, EndDateTime, MINUTE ) / 60 * 100,
                IF (
                    StartDateTime >= SpecifiedStartTime
                        && StartDateTime < SpecifiedEndTime
                        && EndDateTime > SpecifiedEndTime,
                    DATEDIFF ( StartDateTime, SpecifiedEndTime, MINUTE ) / 60 * 100
                        + DATEDIFF ( SpecifiedEndTime, EndDateTime, MINUTE ) / 60 * 200,
                    IF (
                        StartDateTime >= SpecifiedEndTime,
                        DATEDIFF ( StartDateTime, EndDateTime, MINUTE ) / 60 * 200
                    )
                )
            )
        )
    )
RETURN
    Result

6. Cree un objeto visual de tabla.

salary.PNG

Para obtener más información, compruebe el archivo PBIX adjunto.

Saludos

Icey

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

Hola @Dharani_98 ,

Yo creo una medida, no una columna calculada. Por favor, compruebe:

1. Cree la columna [StartDate].

StartDate = DATEVALUE('Table'[StartDate and Time])

startdate.PNG

2. Cree una tabla Calendario.

Calendar = 
CALENDAR (
    MINX ( 'Table', DATEVALUE ( 'Table'[StartDate and Time] ) ),
    MAXX ( 'Table', DATEVALUE ( 'Table'[End Date And Time] ) )
)

calendar.PNG

3. Crear relación.

relationships.jpg

4. Cree la medida [Rank_].

Rank_ =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[StartDate] ) ),
    ,
    ASC,
    DENSE
)

5. Crear [Medida de salario].

Salary Measure = 
VAR LastRank = [Rank_] - 1
VAR LastStartDateTime =
    CALCULATE (
        MAX ( 'Table'[StartDate and Time] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Rank_] = LastRank )
    )
VAR LastEndDateTime =
    CALCULATE (
        MAX ( 'Table'[End Date And Time] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Rank_] = LastRank )
    )
VAR LastDateDiff =
    DATEDIFF ( LastStartDateTime, LastEndDateTime, DAY )
VAR ThisDateDiff =
    DATEDIFF (
        MAX ( 'Table'[StartDate and Time] ),
        MAX ( 'Table'[End Date And Time] ),
        DAY
    )
VAR StartDateTime =
    IF (
        LastDateDiff = 1,
        CONVERT (
            DATEVALUE ( MAX ( 'Table'[StartDate and Time] ) ) & " "
                & TIME ( 0, 0, 0 ),
            DATETIME
        ),
        MAX ( 'Table'[StartDate and Time] )
    )
VAR EndDateTime =
    IF (
        ThisDateDiff = 1,
        CONVERT (
            DATEVALUE ( MAX ( 'Table'[End Date And Time] ) ) & " "
                & TIME ( 0, 0, 0 ),
            DATETIME
        ),
        MAX ( 'Table'[End Date And Time] )
    )
VAR SpecifiedStartTime =
    CONVERT (
        DATEVALUE ( MAX ( 'Table'[StartDate and Time] ) ) & " "
            & TIME ( 8, 0, 0 ),
        DATETIME
    )
VAR SpecifiedEndTime =
    CONVERT (
        DATEVALUE ( MAX ( 'Table'[StartDate and Time] ) ) & " "
            & TIME ( 17, 0, 0 ),
        DATETIME
    )
VAR Result =
    IF (
        LastDateDiff = 1,
        IF (
            EndDateTime > SpecifiedStartTime
                && EndDateTime <= SpecifiedEndTime,
            DATEDIFF ( StartDateTime, LastEndDateTime, MINUTE ) / 60 * 200
                + DATEDIFF ( MAX ( 'Table'[StartDate and Time] ), EndDateTime, MINUTE ) / 60 * 100,
            IF (
                EndDateTime > SpecifiedEndTime,
                DATEDIFF ( StartDateTime, LastEndDateTime, MINUTE ) / 60 * 200
                    + DATEDIFF ( MAX ( 'Table'[StartDate and Time] ), SpecifiedEndTime, MINUTE ) / 60 * 100
                    + DATEDIFF ( SpecifiedEndTime, EndDateTime, MINUTE ) / 60 * 200
            )
        ),
        IF (
            LastDateDiff <> 1,
            IF (
                StartDateTime >= SpecifiedStartTime
                    && EndDateTime <= SpecifiedEndTime,
                DATEDIFF ( StartDateTime, EndDateTime, MINUTE ) / 60 * 100,
                IF (
                    StartDateTime >= SpecifiedStartTime
                        && StartDateTime < SpecifiedEndTime
                        && EndDateTime > SpecifiedEndTime,
                    DATEDIFF ( StartDateTime, SpecifiedEndTime, MINUTE ) / 60 * 100
                        + DATEDIFF ( SpecifiedEndTime, EndDateTime, MINUTE ) / 60 * 200,
                    IF (
                        StartDateTime >= SpecifiedEndTime,
                        DATEDIFF ( StartDateTime, EndDateTime, MINUTE ) / 60 * 200
                    )
                )
            )
        )
    )
RETURN
    Result

6. Cree un objeto visual de tabla.

salary.PNG

Para obtener más información, compruebe el archivo PBIX adjunto.

Saludos

Icey

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Muchas gracias. Fue una gran ayuda.

Greg_Deckler
Super User
Super User

Bueno, en general usarádate DATEDIFF con HOUR

Los datos de ejemplo publicados como texto serían geniales.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Gracias por su respuesta. Cuando estoy usando la hora no puedo obtener mis cálculos para el día 2 que debe ser 4:30 horas. Como trabajar con la hora elimina los minutos en el cálculo.

Por favor, consulte este post sobre cómo obtener su pregunta respondida rápidamente: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors