Trabajar con varias claves de fecha en la tabla

[Syndicate_Admin]

Hola

Tengo en la tabla A dos claves de fecha con formato AAAAMMDD (CreateDateKey y CloseDateKey).

Hay otra entidad llamada Calendario con DateKey formateado también AAAAMMDD y Fecha en formato de fecha.

Mi objetivo es calcular el tiempo promedio para terminar en días, que es el promedio de (CloseDate - CreateDate ---> tiempo tardado en terminar).

Pude realizar esto creando columnas adicionales CreateDate y CloseDate usando las funciones izquierda y derecha, substracting dates con datediff(measure) y luego realizando averagex en la medida datediff como se muestra a continuación. Ni siquiera usé la tabla Calendario para esto.

Average time to complete a task = 

VAR TIMETAKENTOFINISH = 

datediff(
max('DWH DIM_CLAIM'[ClaimCreateDate]),
max('DWH DIM_CLAIM'[ClaimClosedDate]),day)

RETURN

AVERAGEX('DWH DIM_CLAIM',TIMETAKENTOFINISH)

La solución anterior funciona, pero requiere agregar dos columnas de fecha basadas en claves de fecha.

Mi pregunta es, ¿hay alguna manera de tener el mismo resultado sin agregar columnas adicionales (transformando la clave de fecha a la fecha, lo que probablemente no sea eficiente)? Encontré una función USERELATIONSHIP pero no encontré una solución usándola.

¡Gracias de antemano!

[Syndicate_Admin]

Sí, se recomienda USERELATIONSHIP aquí.

Proporcione datos de muestra desinfectados que cubran completamente su problema. Solo puedo ayudarlo con datos de muestra significativos.
Pegue los datos en una tabla en su publicación o use uno de los servicios de archivos como OneDrive o Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Muestre el resultado esperado en función de los datos de muestra que proporcionó. Las capturas de pantalla del resultado esperado están bien.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Syndicate_Admin]

@lbendlin ,

Gracias por su respuesta.

He actualizado el mensaje de publicación anterior con datos de muestra y resultado esperado.

¡Gracias por su ayuda!

[Syndicate_Admin]

Lo siento por mencionar USERELATIONSHIP - no es necesario en absoluto. Puede hacer los cálculos en Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDIyMDQyQDCNlGJ1opWM4AIGpsjKQHLGCDkzdDkTuIChCbocwiBDDDMRBhlimGmOkLNEkYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Key" = _t, CreateDateKey = _t, CloseDateKey = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From([CloseDateKey])-Date.From([CreateDateKey])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type"

Cómo usar este código: Crear una nueva consulta en blanco. Haga clic en "Editor avanzado". Reemplace el código en la ventana con el código proporcionado aquí. Haga clic en "Listo".

[Syndicate_Admin]

Oye, gracias por tu respuesta.
Eso es todo, que necesito un uso completo de DAX sin crear columnas de cálculo, solo en función de las medidas. La solución PQ no cumple con mis expectativas 😕 de rendimiento

[Syndicate_Admin]

En DAX puede utilizar el mismo enfoque.

Measure = AVERAGEX('DWH DIM_CLAIM',
 DATEVALUE(Left([CloseDateKey],4) & "-" & MID([CloseDateKey],5,2) & "-" & RIGHT([CloseDateKey],2))
-DATEVALUE(Left([CreateDateKey],4) & "-" & MID([CreateDateKey],5,2) & "-" & RIGHT([CreateDateKey],2)))

[Syndicate_Admin]

@lbendlin ,

¡Eso funciona perfectamente bien! Datevalue era una función que no conocía.

Una cosa más es que hay una razón por la que existe la tabla Calendario.
¿Existe un enfoque en el que podamos usar la tabla Calendario para trabajar realmente en las relaciones / Claves? Es solo debido a algunas CloseDateKey que aún no son fecha (la clave -1 es un valor en blanco en la tabla del calendario, lo que significa que algo aún no se ha terminado / cerrado). ¿Qué te parece?
Si no suena posible / razonable, aceptaré su solución anterior.

Creo que también podría hacer una condición con su última medida para evitar contar si CloseDateKey es -1, pero para algunos propósitos de aprendizaje me gustaría saber si hay una manera de realizar esto usando claves / relaciones como se mencionó anteriormente.

[Syndicate_Admin]

Esto es entrar en el territorio de unión exterior y USERELATIONSHIP. Sin buenos datos de muestra (y un poco más de contexto sobre lo que está tratando de lograr y cómo debería ser el resultado esperado) será difícil ayudar más.

[Syndicate_Admin]

Seguro @lbendlin ,
Mal tratar una vez más de explicar esto con más contexto.
Hay datos de muestra:
https://drive.google.com/file/d/164xWVAwiiVgbEeBls9JG0Rhjas9qmUDb/view?usp=sharing

En la ficha puedes encontrar medida. Esta salida de la medida es correcto.


Pero, por otro lado, me gustaría encontrar otra solución (para el análisis de rendimiento y el aprendizaje) utilizando la tabla Calendario sin crear columnas adicionales y relaciones directas en el modelo de datos.
Pasos de medida:
1. Busque Fecha en la tabla Calendario por DateKey para CreateDateKey y CloseDateKey.

2. Reste las fechas iterando a través de cada fila.

3. Realizar averagex en filas restadas

4. Todo dentro de una misma medida.

[Syndicate_Admin]

1. Find Date in Calendar table by DateKey for CreateDateKey and CloseDateKey.

Es o sea o. No puede tener ambas fechas vinculadas con la tabla de calendario en una relación activa. Uno de ellos tiene que estar inactivo.

[Syndicate_Admin]

Aight, así que userelationship no resolverá esto. gracias

[Syndicate_Admin]

¿Qué pasa con 2 tablas de fechas? CreatedDate y ClosedDate

Con respecto a los registros sin fecha cerrada: ¿Sería útil usar PowerQuery para reemplazar los valores nulos con la clave de fecha para la FECHA ACTUAL?

Si es así, considere probar esto.

pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPFMNE3v3SoIIN_0AQ?e=lNjGWl

[Syndicate_Admin]

Eso está completamente bien. Pero ahora tengo mi opinión más clara.

Mi idea es relacionar dos claves de fecha con la tabla de calendario dentro de la medida. Podría imaginar esto por digamos que las claves de fecha serían smh como NO 20220813 es un 8/13/2022, pero digamos que la clave 1 sería 8/13/2022 clave 2 8/14/2022, etc.

Pero sí, en mi escenario los datekys son 'transformables' en fecha, por lo que es un problema.
Gracias por la ayuda chicos!