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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Diego_F
Regular Visitor

Dar orden a una dataset para calcular la diferencia entre dos fechas y realizar la sumatoria total

Hola a todos, por favor su ayuda.

Tengo el siguiente dataset con el comportamiento de unos sensores; la tabla muestra la fecha y hora en el que el sensor se apaga (Down) y cuando se enciende (UP).

 

El data set es el siguiente:

 

NameEvent Type NameEvent Time
LocalNode Down29/06/2023 17:35
LocalNode Up29/06/2023 20:25
LocalNode Down1/07/2023 23:03
LocalNode Up1/07/2023 23:05
LocalNode Down3/07/2023 19:29
LocalNode Up3/07/2023 19:31
LocalNode Down3/07/2023 21:37
LocalNode Up 
LocalNode Down5/07/2023 18:56
LocalNode Up5/07/2023 18:59
LocalNode Down6/07/2023 15:56

 

Por favor su ayuda, Se requiere calcular la diferencia entre las dos fechas para cada uno en el que se pueda realizar la operacion y al finalizar sumar el total de tiempo en el que sesnsor estuvo apgado, de esta forma como se muestra en la siguiente tabla:

 

NameNode DownNode UpDiferencia( segundos)
Local29/06/2023 17:3529/06/2023 20:2510246,28
Local1/07/2023 23:031/07/2023 23:05156,10
Local3/07/2023 19:293/07/2023 19:31135,11
Local3/07/2023 21:37  
Local5/07/2023 18:565/07/2023 18:59161,24
Local6/07/2023 15:56  
Total  10698,733

 

Nota:

Como se puede observar algunas veces el sensor solo registra un estado o UP o Down, este se omitira ya que no se puede realizar la operacion matematica de la diferencia.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Diego_F ,

 

Here are the steps you can follow:

1. In Power Query -- Add Column – Index Column – From 1.

vyangliumsft_0-1691461103262.png

2. Add Column – Conditional Column .

vyangliumsft_1-1691461103266.png

3. Select [Custom] – Fill -- Down.

vyangliumsft_2-1691461125163.png

4. Select [Index] – Remove.

vyangliumsft_3-1691461125165.png

5. Select [Name] and [Custom] – Merge Columns.

vyangliumsft_4-1691461148136.png

6. Select [Event Type Name] – Transform – Pivot Column.

vyangliumsft_5-1691461148139.png

vyangliumsft_6-1691461164073.png

7. Result:

Your [Diferencia(segundos)] is not very clear how it came about, and the difference between the two dates I got using the DATEDIFF() function is the following:

vyangliumsft_7-1691461175632.png

 

Best Regards,

Liu Yang

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

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Diego_F ,

 

Here are the steps you can follow:

1. In Power Query -- Add Column – Index Column – From 1.

vyangliumsft_0-1691461103262.png

2. Add Column – Conditional Column .

vyangliumsft_1-1691461103266.png

3. Select [Custom] – Fill -- Down.

vyangliumsft_2-1691461125163.png

4. Select [Index] – Remove.

vyangliumsft_3-1691461125165.png

5. Select [Name] and [Custom] – Merge Columns.

vyangliumsft_4-1691461148136.png

6. Select [Event Type Name] – Transform – Pivot Column.

vyangliumsft_5-1691461148139.png

vyangliumsft_6-1691461164073.png

7. Result:

Your [Diferencia(segundos)] is not very clear how it came about, and the difference between the two dates I got using the DATEDIFF() function is the following:

vyangliumsft_7-1691461175632.png

 

Best Regards,

Liu Yang

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

Hello Yang.
Thank you, it was what I was looking for, I will implement it and I will inform you if any doubt appears.

amitchandak
Super User
Super User

@Diego_F , Try a measure like

 


Var _min = Maxx(filter(allselected(Table),Table[Name] = maX(Table[Name]) && Table[Event Time] > Max(Table[Event Time])), Table[Event Time])
var _type = Maxx(filter(allselected(Table),Table[Name] = maX(Table[Name]) && Table[Event Time]=_min && Table[Event Type] = "Up"), Table[Event Time])
return
if( Table[Event Type] = "Down", datediff(Max(Table[Event Time]) ,_min second))

Hola, gracias, estoy intentando agregarla pero el parametro:

if( unido[Event Type name] = "Node Down", datediff(Max(unido[Event Time]) ,_min second))

No logro ajustarlos, podria guiarme porfavor.

 

medida.PNG

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors