Calcular la relación de suma de cada duración del estado Frente a la duración del tiempo operativo planificado

[Syndicate_Admin]

Hola

Actualmente estoy teniendo problemas con el cálculo de la relación entre la duración del tiempo de los estados de una máquina y la duración del tiempo operativo planificada.

La tabla PlannedOperationalActivity contiene la hora de inicio y finalización planificada del equipo.

PlannedOperationalActivityID	DeviceID	LunesStartDateTime	LunesEndDateTime	MartesStartDateTime	MartesEndDateTime	MiércolesStartDateTime	MiércolesEndDateTime	JuevesStartDateTime	JuevesEndDateTime	ViernesStartDateTime	ViernesEndDateTime
1	1	09:00:00	18:00:00	09:00:00	18:00:00	09:00:00	18:00:00	09:00:00	18:00:00	09:00:00	18:00:00
2	2	09:00:00	18:00:00	09:00:00	18:00:00	08:30:00	17:30:00	09:00:00	18:00:00	09:00:00	18:00:00
3	3	09:00:00	18:00:00	09:00:00	13:00:00	09:00:00	18:00:00	09:00:00	13:00:00	09:00:00	13:00:00

La tabla DeviceOperationalState contiene qué estado se ha producido y cuánto tiempo estuvo activo el estado.

DeviceOperationalStateID	DeviceID	OperativoStateID	StateDateTimeStart	StateDateTimeEnd
1	1	7	02/01/2021 08:00	02/01/2021 09:00
2	1	4	02/01/2021 09:00	02/01/2021 11:14
3	1	3	02/01/2021 11:14	02/01/2021 14:05
4	1	5	02/01/2021 14:05	02/01/2021 14:10
5	1	4	02/01/2021 14:10	02/01/2021 18:00
6	1	8	02/01/2021 18:00	02/01/2021 18:31
7	3	7	21/01/2021 08:00	21/01/2021 09:00
8	3	4	21/01/2021 09:00	21/01/2021 18:00
9	3	8	21/01/2021 18:00	21/01/2021 19:00

Lo que quiero calcular es la suma de tiempo para cada estado de un DeviceID por un día y lo devide por la duración entre la duración planificada del día laborable correspondiente.

Un ejemplo es 21/01/2021 de DeviceID 3, que es un jueves. La suma de las duraciones de estado es 60+540+60=660mins

La actividad operativa prevista es ThursdayEndDateTime-ThursdayStartDateTime = 18:00:00-09:00:00 = 09:00:00 = 540mins.

La relación es de 660/540*100 = 122%. StatusID 7 = 11%, StatusID 4=100% y StatusID 8 = 11%.

En un gráfico quiero mostrar por día del mes que la máquina estaba activa cuál era la relación del tiempo operativo planificado frente al tiempo real en el que la barra de tiempo activa real total se divide en segmentos por estado.

La ayuda es muy apreciada.

[Syndicate_Admin · Accepted solution]

No @Karel,

Estos son los pasos que puede seguir:

1. Hacer clic Transformar datos para entrar en la consulta de energía, seleccione [DeviceID], haga clic transformar – Columnas unpivot – Otras columnas unpivot

resultado:

2. Cree una columna calculada.

datediff = DATEDIFF([StateDateTimeStart],[StateDateTimeEnd],MINUTE)

resultado:

3. Cree una tabla calculada.

Summarize =
SUMMARIZE('Table (2)',[DeviceID],"Time duration",SUM('Table (2)'[datediff]),"Weekday",FORMAT(WEEKDAY(MAX('Table (2)'[StateDateTimeStart]),1),"dddd"))

resultado:

4. Cree una columna calculada.

Percent1 =
var _total=
SUMX(FILTER(ALL('Table (2)'),[DeviceID]=EARLIER('Table (2)'[DeviceID])),[datediff])
var _percent=
DIVIDE([datediff],_total)
var _related=
CALCULATE(MAX('Summarize'[Percent]),FILTER(ALL('Summarize'),[DeviceID]=EARLIER('Table (2)'[DeviceID])))
return
_related*_percent

5. Resultado:

Saludos

Liu Yang

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

[Syndicate_Admin]

@Karel tiempo diff = DATEDIFF(Sheet1[Hora de inicio],Sheet1[Hora de finalización],MINUTE)

[Syndicate_Admin]

Hola @HarishKM,

Gracias por responder.

Mientras sigo tus instrucciones me he quedado atascado una vez más.

Su solución está creando una medida para el tiempo operativo, suponiendo que se refiere a la misma fórmula (

TimeDiffOperationalState = DATEDIFF(DeviceOperationalState[StateDateTimeStart],DeviceOperationalState[StateDateTimeEnd],MINUTE)). Esta fórmula no es posible porque la medida no acepta nada más que una medida. Creé otra columna calculada en su lugar con la misma fórmula.

Al crear una medida para dividir ambas duraciones de tiempo, ambas columnas calculadas no se pueden seleccionar.

RatioTimeDurations = DIVIDE(PlannedOperationalActivity[ThursdayTimeDiffMin],DeviceOperationalState[TimeDiffOperationalStateColumn])

Además, esta fórmula sólo tendría en cuenta la diferencia horaria si el día de la semana es jueves. Si la fecha es el 22 de Januari 2021, la fórmula debe utilizar viernes Inicio y Fin como relación y si es el 20 de Januari 2021 la fórmula debe utilizar la diferencia horaria del miércoles como relación.

Intento adjuntar el archivo PBI, pero no se me permite.