PROMEDIO de MTD diario y MTD diario promedio el año pasado para todos los días laborables

[Reine]

Hola a todos - Estoy atascado en mi limitado conocimiento DAX y desesperadamente necesito un poco de ayuda!

Estoy tratando de crear un visual dinámico que muestre nuestras citas diarias promedio (por ubicación) MTD y MTD el año pasado para ayudarnos a ver cómo las restricciones COVID 19 nos están afectando. Tengo una tabla de datos que tiene todas las citas junto con la fecha appt, el identificador de appt y la ubicación y también tengo una tabla de fechas. Agregué una columna "isworkingday" a mi tabla de fechas que también tiene en cuenta los días festivos y que está funcionando.
tabla de fechas de muestra

Si estás a buen punto, me dieron cuenta de que sólo los días que tienen citas y lo necesito para calcular todos los días de trabajo en su lugar. Después de horas de probar varias cosas, simplemente no puedo averiguar qué actualizar para que esto funcione. Mi conocimiento de DAX sigue siendo limitado, por lo que hay partes de las siguientes fórmulas que simplemente no entiendo, así que no sé cómo cambiarlo correctamente.

Aquí está el DAX actual que estoy usando:

Promedio diario ?

VAR cntAppointments - COUNTROWS(DISTINCT(AppointmentsT[ID]))

VAR cntDates á COUNTROWS(DISTINCT(AppointmentsT[Date]))

devolución

DIVIDE(cntAppointments,cntDates)

AVG MTD ?

VAR StartDate ?FECHA(AÑO(HOY()), MES (HOY()),1)

FECHA De Findeda de VAR ?HOY()

devolución

CALCULAR([Avg Daily],ALL('Fecha'[Fecha]),

Y (CitasT[Fecha] >- Fecha De Inicio,CitasT[Fecha] <- EndDate))

Promedio diario Appts Mes actual 2019 ?

VAR StartDate ?FECHA(AÑO(HOY())-1,MES(HOY()),1)

FECHA De Findeda de VAR ?EOMONTH(StartDate,0)

devolución

CALCULAR([Avg Daily],

ALL('Fecha'[Fecha]),

Y (CitasT[Fecha] >- Fecha De Inicio,CitasT[Fecha] <- EndDate))

% avg daily appts estamos abajo este año vs último = [AVG MTD]/[Avg Daily Appts Current Month 2019]*1

AVG aappts todos los 2019

VAR StartDate ?FECHA(2019,1,1)

FECHA De Findeda de VAR ?FECHA(2019,12,31)

devolución

CALCULAR([Avg Daily],ALL('Fecha'[Fecha]),

Y (CitasT[Fecha] >- Fecha De Inicio,CitasT[Fecha] <- EndDate))

Este es el objeto visual actual usando todo el DAX anterior quesólo días que tenían citas. Esto se actualiza automáticamente a medida que se programan o cancelan las citas:

Gracias de antemano por la ayuda - Estoy tirando de mi pelo por aquí 😞

[Anonymous]

hola @Reine : lo que un problema pegajoso! Suponiendo que exista una relación entre la tabla de datos y la tabla de fechas, solo cambie la siguiente parte de la fórmula.

OLD: VAR cntDates - COUNTROWS(DISTINCT(AppointmentsT[Date]))

NUEVO: VAR cntDates - SUM(DateTable[IsWorkingDay])

La razón por la que las cosas están fallando para usted es que solo está contando las fechas en su tabla de citas, no en su tabla de fechas.

[Reine]

@toolatejake
Siento que tiene algo que ver con la parte "ALL" de la fórmula en las otras medidas, pero no entiendo muy bien lo que está haciendo así que no sé cómo cambiarlo.

[Anonymous]

Sí, la fórmula ALL haría que el denominador fuera demasiado grande, que es lo que estamos viendo. El problema aquí es que DAX calcula y los ámbitos relativos a su modelo de datos, por lo que la solución que utilizó es para un modelo de datos diferente, de ahí los problemas que está viendo. Así que, por desgracia, no es tan simple como "qué fórmulas arreglo", es un problema de dos pasos de "lo que mi modelo de datos tiene que hacer para cumplir con mi objetivo" y luego "qué medidas necesito usar en este modelo".

Voy a echar un vistazo a esto cuando tengo un poco más de tiempo (más tarde hoy), y maqueta de un modelo de datos + medidas que le permitirá obtener los datos correctos en la tabla de salida.

[Reine]

@toolatejake gracias y no te preocupes - He estado tratando de hacer esto durante 2 semanas, así que otro par de días no importa 🙂 tal vez no entiendo realmente lo que significa un modelo de datos. Me conecto a nuestro software de gestión de prácticas y tiene una tabla de citas que utilizo como es

[Anonymous]

@Reine: Ok, así que después de dar un paso atrás y pensar en esto, sé por qué mi solución propuesta no funcionó. No está utilizando filtros en los datos de PowerBI directamente, está aplicando filtros dentro de DAX. ¿Qué está bien, supongo? Esto significa que si sugiero una consulta en la tabla de fechas, que no está restriccionada, obtendrá un denominador demasiado grande.

De todos modos, me burlé de los datos y simplifiqué esto para que mi simple cerebro pueda entenderlo.

https://1drv.ms/u/s!AhFUa5zn5xJwwC5si9mH3b-QPLZw?e=gS34dM

Creé un nuevo modelo, con el hecho, la fecha y la tabla de dimensiones para la ubicación. Usted notará que sólo estoy exponiendo la tabla tenue y las medidas, queremos ocultar todas las otras mesas para que no las usemos accidentalmente.

ok así que creé una serie de dimensiones - la buena noticia es que si usted está familiarizado con excel en todo lo que inmediatamente entenderá

Ellos.

y una última nota, agregué algunos campos nuevos a la tabla de calendario de fechas. Una simple declaración IF a cada fecha para comprobar si es el mes actual, el mismo mes del año pasado y el año pasado. ¡Eso hace que nuestras transacciones COUNT sean súper simples!

Ejemplo del nuevo DAX:

CM_Appt calculate(COUNT(fact_Appt[ApptID]),'date'[CurrentMonth]-"Yes")

No necesita distinto, no necesita ALL(), no necesita FILTRO. Echa un vistazo y hazme saber tus pensamientos.

[Reine]

@toolatejake gracias por tomarse el tiempo para hacer esto. Es muy útil ver otra manera de hacer esto. Sin embargo, algo todavía no está bien. Me parece que los cálculos son para todo el mes actual (este año y último) en lugar de MTD actual - ¿es eso correcto? Lo que necesito es que siempre muestre el mes actual hasta la fecha - y cambiará automáticamente con cada día adicional del mes y comenzará de nuevo cuando comience el mes siguiente. ¿Tiene sentido?

[Anonymous]

@Reine: Correcto que eres.

Para solucionar lo, solo necesitamos dos cambios de fórmula. Lo he guardado en un archivo de unidad vinculado anteriormente también si quieres verlo.

PriorYearCurrentMonth ?
SI (
Y (
Y (
Año ( HOY () ) - 1
• Año ( 'fecha'[fecha] ),
MES ( HOY () ) ? MES ( 'fecha'[fecha] )
),
DIA ( 'fecha'[fecha] ) <-DÍA DAY ( HOY () )
),
"Sí",
"No"
)

Meses corrientes ?
SI (
Y (
EOMONTH ( HOY (), 0 ) - EOMONTH ( 'fecha'[fecha], 0 ),
'fecha'[fecha] <-HOY () TODAY
),
"Sí",
"No"
)

[Reine]

@toolatejake Hola - el archivo no parece estar actualizado? Se ve igual para mí - lo siento si me lo estoy perdiendo. (Aunque veo vid oso! se parece a PNC para mí)

No tengo claro qué medidas debo reemplazar con estas. ¿Estos dos cambios se retrasarán todos los días en MTD o solo mis días laborables?

[Anonymous · Accepted solution]

@Reine: PNW de hecho 🙂

Lo siento por la confusión - hay un montón de matices a cómo resolver su problema y es difícil capturar todo eso por escrito. Así que lo intentaré de nuevo. El archivo se actualiza - enlace compartido de nuevo para el contexto - https://1drv.ms/u/s!AhFUa5zn5xJwwC5si9mH3b-QPLZw?e=dgeHZy

Lo que hice primero es maquear sus datos en Excel y luego importarlos a PowerBI. Luego hice algunas transformaciones de luz en la consulta de potencia para adaptar el modelo a un modelo de datos de "fact-dim-measure". Puede verlos mirando la vista "Transformaciones" en PowerBI.

Lo que no es obvio, sin embargo, es que agregué 3 columnas calculadas a la tabla de fechas en PowerBI directamente. Esas columnas son donde se encuentran las fórmulas para determinar si una fecha coincide con el mes actual hasta la fecha, el año anterior mes a la fecha y el año anterior.

Luego, una vez que tenía mis datos preparados, comencé a agregar medidas.

Para las citas, estamos tomando el recuento de apptID en un cierto rango. Este patrón se repite para todas las medidas relacionadas con appt.

Para los días laborables, estamos tomando la suma de "WorkDay", que es un campo calculado que devuelve un 1 o un 0 basado en si un día es un fin de semana o no (reconozco que lo está haciendo de manera diferente con las vacaciones, pero estoy ignorando ese matiz). Este patrón se repite de nuevo para todos los campos relacionados con el día laborable.

Así que para responder a la pregunta, ¿estos cambios se retrasarán a todos los días? No, porque todas las medidas hacen referencia a suma de días laborables, no días totales. ¿Y debería cambiar de medida? No, debe cambiar las columnas calculadas en la tabla de fechas como se indicó anteriormente.

Espero que encuentre solución de esto - usted está tan cerca de resolver esto y en este punto me siento muy comprometido a ayudar a cerrarlo!

[Reine]

@toolatejake lo siento - Tenía medidas en el cerebro y pensé que para eso eran las fórmulas. Sí, me había dado cuenta de las adiciones a la tabla de fechas y las hice originalmente. Ahora he actualizado esos dos y funciona perfectamente! Me parece que ahora que esto se actualizará automáticamente todos los días y / o como mi cita cuenta cambia y no voy a tener que hacer nada - yay!

Acabo de darme cuenta de algo que arroja una llave inglesa en las cosas. 😞 Uno de los lugares en realidad tiene días laborables diferentes que todos los demás. Esa ubicación está cerrada los viernes, además de los fines de semana y días festivos. Lo siento tanto - Estaba tan concentrado en el DAX que ni siquiera pensé en esa ubicación que tiene días de trabajo diferentes. Ugh. ¿Podría crear otra columna en mi tabla de fechas para "IsWorkingDayLocation4"? ¿Y de alguna manera usar eso para una sola compañía? Todavía necesito que mi visual incluya todas las ubicaciones como lo hace.

No puedo decirte cuánto aprecio tu ayuda con esto. He pasado tantas horas tratando de resolver esto. Lo que realmente necesito hacer es un entrenamiento real. Tengo los conceptos básicos bajos, pero necesito entender MEJOR DAX y modelar. ¿Tiene alguna experiencia con los cursos de formación de Enterprise DNA?

[Anonymous]

Recomiendo la Chandoo.org la formación, no puedo hablar por otros cursos.

la solución para su problema de ubicación requiere algo llamado una tabla de puentes (o DAX más complejo). Prefiero mantenerlo simple, así que construiré el puente y compartiré contigo.

[Anonymous]

@Reine: Ok una vez más.

El archivo se actualiza de nuevo - https://1drv.ms/u/s!AhFUa5zn5xJwwC5si9mH3b-QPLZw?e=43Y4lZ

Agregué una mesa de puente, que se ve así:

A continuación, agregué una "JoinKey" a la tabla de puentes y la tabla de hechos, ya que no se pueden crear relaciones en varias columnas en powerBI. Esa es la columna de extrema derecha de la tabla anterior. Esto facilita la relación de estas tablas a continuación:

Como último paso, todas las medidas que hacen referencia a 'fecha'[WorkDay] se actualizaron para hacer referencia a 'bridge_DateLocation'[WorkDay].

Ejemplo: CM_WorkDays : CALCULATE(SUM(bridge_DateLocation[WorkDay]),'date'[CurrentMonth]"Yes")

Quiero enfatizar esto - aquellos que son mejores en DAX que yo podrían escribir una sola expresión DAX para resolver esto. No soy tan bueno como esas personas, así que tengo que confiar en la materialización de datos provisionales en mis modelos de datos. Sin embargo, esto me resulta infinitamente más fácil de depurar.

[Reine]

@toolatejake yikes. ¡Esto es aún más difícil para mí envolver mi cabeza que DAX! Creo que tomará un poco para recrear esto en mi pbix ya que necesito buscar cómo hacer una mesa separada como has hecho y algunas cosas son un poco diferentes (no tengo una tabla de ubicaciones separadas) pero creo que puedo trabajar a través de cómo hacerlo. Muchas gracias por toda su ayuda. Después de semanas tratando de averiguar el DAX, me siento muy aliviado de tener una solución de trabajo. ¡Eres un salvavidas!

[Anonymous]

Eche un vistazo al código en la sección "Transformaciones" de PowerBI y puede copiarlo para crear una tabla de ubicación.

El flujo de trabajo es una referencia a cualquier tabla que tenga toda la ubicación en ella como una columna, quite todas las demás columnas ubicación de excepción (y sus otros atributos de ubicación, es decir, nombre o descripción) y, a continuación, quite las filas duplicadas. El código es el siguiente para cómo creé una tabla de ubicación.

Dejar
Fuente: fact_Appt,
"Eliminado otras columnas" - Table.SelectColumns(Source,"LocationID")),
"Eliminado Duplicados" - Table.Distinct(-"Eliminados Otras Columnas"),
"Filas ordenadas" - Table.Sort("Duplicados eliminados","LocationID", Order.Ascending),
En
"Filas ordenadas"

[Reine]

@toolatejake Las "transformaciones" (¿verdad) son todos errores porque en realidad no se conectan a los datos que creo.

Tengo que admitir que no creé una tabla de "ubicación" separada porque no pensé que necesitaba ya que está en mi tabla de datos y todo parece funcionar sin crear una tabla separada. ¿Hay alguna razón detrás de hacer eso que no estoy entendiendo?

[Anonymous]

La creación de un modelo de datos de patrón de "medida de hecho", hará que sea más comprensible para los usuarios de datos. No tienes que hacerlo.

Más sobre el "esquema de estrellas" se puede encontrar aquí - https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[Reine]

@toolatejake gracias - Voy a leer sobre el esquema de la estrella. Un modelo de hecho/dim/measure tiene sentido para mí. Nunca lo había pensado de esa manera. Voy a echar un vistazo a Chandoo también. He estado usando power bi con una mentalidad de Excel y justo lo que puedo hacer con el tipo de arrastrar y soltar características fáciles de Power Bi. Ahora que estoy tratando de llevar las cosas al siguiente nivel me doy cuenta de que necesito hacer una copia de seguridad y obtener una mejor comprensión del modelado, DAX, etc. Aprecio mucho todo el tiempo que has pasado ayudándome - tanto me da las respuestas, así como explicar de una manera que realmente me enseña algunas cosas!

[Reine]

@toolatejake oops typo - Me refería a PNW 🙂

[Reine]

Gracias por responder @toolatejake . Desafortunadamente esto no está funcionando 😞 cambié esa línea en mi Mid Daily Measure y esto es lo que el visual se ve ahora:
visual con el cambio

Tengo una relación entre los datos y la tabla de fechas: