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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luisalbertojb
Frequent Visitor

Calcular duración de tiempo entre 2 fechas

Buenos días,

Tengo una tabla con registros de detenciones de equipos, la cual registra la fecha/ hora de inicio y la fecha/hora final, obtener la duración total de la detención en horas es fácil, el problema es cuando me piden que filtre entre 2 fechas, dado que existe detenciones que iniciarion antes de la fecha inicio del filtro, pero acabaron en el rango del filtro y existen otras que empezaron después de la fecha de inicio del filtro, pero acabaron después del rango, en ambos caso igual necesito contabilizar el pedazo de tiempo de este registro que se encuentre dentro del rango.

 

Por ejemplo: Tengo un equipo que paró el 25/12/2018 y salió operativo el 05/01/2019, la duración total de la detención en horas sería 264h. Si yo coloco un filtro y digo que quiero todos los registros desde el 01/01/2019 al 23/01/2019, para empezar en POWER BI no se como jalar este registro ya que la fecha de inicio de la detención no coincide con la fecha de inicio del filtro, en segundo lugar no sé como obtener la duración total de la detención aplicando el filtro, en Excel lo calculo y obtengo 96 horas que sería el resultado de restar la fecha final de la detención (05/01/2019) menos la fecha inicio del filtro (01/01/2019).

 

En la imagen inferior coloco algunos ejemplos de como obtengo la duración total con filtro en Excel y en Power BI como traté con un campo calculado y 2 métricas.

 

PrintScreen.jpg

FI_Filtro = MIN(Calendario[Date])
FF_Filtro = MAX(Calendario[Date])+1
 
De antemano muchas gracias por cualquier ayuda que me puedan brindar.
Luis J.
1 ACCEPTED SOLUTION

Hi @luisalbertojb ,

You could create another measure using SUMX function to implement it. However, the results of "Measure" contain text. The Measure 2 only can get blank value when it is "False" in "Measure" and get the total values of others.

Measure 2 = SUMX('Table',[Measure])

4.PNG 

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-xuding-msft
Community Support
Community Support

Hi @luisalbertojb ,

I think you want to calculate the total hours when the start date or the end date is within the filter date range. If so, maybe the following formula can help you.

  • Create a calendar table (as a slicer)
Date = CALENDARAUTO()

1.PNG

There is no relationship between the tables.

  • Create a measure
Measure =
CALCULATE (
    DATEDIFF ( MAX ( 'Table'[StartDate] ), MAX ( 'Table'[EndDate] ), MINUTE ) / 60,
    FILTER (
        'Table',
        'Table'[StartDate] >= MIN ( 'Date'[Date] )
            || 'Table'[EndDate] <= MAX ( 'Date'[Date] )
    )
)

 2.PNG

I translate your description with Google Translate. If I misunderstand, please point it out.

 

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your prompt response.

But I still can't get only part of the time.

 

Example: My filter is since 01-Jan-2019 (StartDate) until 23-Jan-2019 (EndDate), but my register of detention is since 25-Dec-2018 (StartDateDetention) until 05-Jan-2019 (EndDateDetention). 

 

I want to get only the part in red that is inside the filter. For my example I just want to get 96h

 

Detentions.jpg

 

Regards, thanks

Hi @luisalbertojb ,

Sorry for misunderstanding, please try this:

Measure 2 =
VAR a =
    DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Table'[EndDate] ), MINUTE ) / 60
VAR b =
    DATEDIFF ( MAX ( 'Table'[StartDate] ), MAX ( 'Date'[Date] ), MINUTE ) / 60
VAR c =
    DATEDIFF ( MAX ( 'Table'[StartDate] ), MAX ( 'Table'[EndDate] ), MINUTE ) / 60
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[StartDate] ) > MAX ( 'Date'[Date] )
            || MAX ( 'Table'[EndDate] ) < MIN ( 'Date'[Date] ), "False",
        MAX ( 'Table'[StartDate] ) <= MIN ( 'Date'[Date] )
            && MAX ( 'Table'[EndDate] ) <= MAX ( 'Date'[Date] ), a,
        MAX ( 'Table'[StartDate] ) >= MIN ( 'Date'[Date] )
            && MAX ( 'Table'[EndDate] ) >= MAX ( 'Date'[Date] ), b,
        c
    )

2.PNG

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much, I got the results. How could I get the sum in the total and not the false value? The total is 477.1h

Hi @luisalbertojb ,

You could create another measure using SUMX function to implement it. However, the results of "Measure" contain text. The Measure 2 only can get blank value when it is "False" in "Measure" and get the total values of others.

Measure 2 = SUMX('Table',[Measure])

4.PNG 

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-xuding-msft 

 

Thanks for you reply, I have the same situation, but I need add a new step, that is get a measure accumulated of measure 2 month to month. your answer would be helpful.

 

regards.

You can add one field per month in your calendar table and then get the accumulatedPrintScreen.jpg

 

Date =
VAR _calendario =
CALENDARAUTO()

RETURN

ADDCOLUMNS (
_calendario,
"Año", YEAR ( [Date] ),
"Semestre",ROUNDUP(DIVIDE(MONTH([Date]),6),0),
"Trimestre", "Q " & FORMAT ( [Date], "Q" ),
"Nro. Trimestre", FORMAT ( [Date], "Q" ),
"Nro. Mes", MONTH ( [Date] ),
"Mes", FORMAT ( [Date], "mmmm" ),
"Nro. Semana", WEEKNUM ( [Date],2 ),
"Dia del Mes", DAY ( [Date] ),
"Nro. Dia de Semana", WEEKDAY ( [Date],2 ),
"Dia de Semana", FORMAT ( [Date], "dddd" ),
"Año Mes", VALUE ( ( YEAR ( [Date] ) & FORMAT ( [Date], "MM" ) ) )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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