Introducción
a mi último artículo, sobre la creación de la solución Like-for-Like (L4L) basada en Power Query:
La solución funciona como se esperaba en su mayor parte. Se lo mostré a mis compañeros y a algunos clientes.
Los comentarios fueron positivos, pero tengo algunas preguntas y los resultados de mi solución no fueron los que esperaba la persona que preguntó.
El problema
Descubrí un problema al calcular el valor PY.
Técnicamente, los resultados son correctos, pero no lo son desde la perspectiva del usuario.
Mire las siguientes dos capturas de pantalla, que muestran dos casos diferentes que incluyen las medidas de Ventas minoristas y PY de Ventas minoristas. Los resultados de estos dos casos pueden confundir a la audiencia.
Intente detectar el problema antes de continuar leyendo.
Se trata del primer caso de la tienda de Turín, que estuvo cerrada temporalmente entre marzo y julio de 2024.
Y aquí está el segundo caso de la tienda Roma, que estuvo cerrada temporalmente de agosto a octubre de 2023 y cerrada definitivamente en agosto de 2024.
Vemos estos resultados para el segundo caso:
Los valores de la medida PY Sales Retail para tiendas “Comparables”, pero con una interrupción entre agosto y octubre. Valores de la medida de Ventas Minoristas para tiendas “No Comparables – En Cierre”. Valores para la medida PY de ventas minoristas para tiendas “No comparables – Actualizar”.
Desde un punto de vista técnico, estos resultados tienen absoluto sentido y son correctos.
Las medidas muestran los estados L4L correctos para el período actual y el año anterior.
Entonces, ¿cuáles son los problemas?
Para el usuario, resultan muy confusos y no cumplirán con las expectativas.
Piénselo desde la perspectiva del usuario:
Al observar los resultados de estados L4L específicos, las dos medidas deben asignar resultados al mismo estado L4L, independientemente de si se calculan para el período actual o el año anterior.
Esto introduce una nueva complejidad a la solución.
la solución
Necesito una segunda columna para L4LKey del año anterior.
Para la primera columna L4LKey, comparo las fechas de apertura y cierre con las fechas mensuales del año anterior (consulte el primer artículo para obtener más detalles).
Para la segunda columna L4LKey_PY, debo comparar estas fechas con las fechas mensuales del mismo año que las fechas de apertura y cierre.
La idea es algo contradictoria, pero ofrece el resultado que necesito.
Por favor, quédate conmigo y verás cómo resulta.
Primero, intenté resolverlo en Power Query, como hice en la solución original. Pero no funcionó. Llegaré a la razón en un minuto.
Luego, cambié a crear la tabla Bridge_L4L en SQL, pero los resultados volvieron a ser inutilizables, ya que siempre obtenía filas duplicadas para la tienda de Roma, ya que tengo dos filas para los dos estados L4L de esta tienda:
Tengo una fila cada una para el cierre temporal en 2023 y el cierre definitivo en 2024.
Por lo tanto, la combinación siempre devuelve dos filas, ya que la clave de la tienda está duplicada.
Entonces decidí cambiar a un enfoque procesal.
Recorro cada fila de la tabla que contiene las tiendas de apertura y cierre y aplico los estados a la tabla, que tiene una fila por tienda y mes.
Hice esto usando tablas temporales en SQL y el siguiente código SQL:
— Declarar todas las variables necesarias DECLARE @StoreKey int; DECLARAR fecha @OpenDate; DECLARAR fecha @CloseDate; DECLARAR @L4LKey int; — Cree el cursor para recorrer las tiendas con cada fecha de apertura, cierre y actualización DECLARE sd CURSOR FOR SELECT [StoreKey]
,[OpenDate]
,[CloseDate]
,[L4LKey]
FROM #tmp_Store_Dates: pedido por fecha de cierre, ya que el procedimiento debe ejecutarse desde la primera (más antigua) hasta la última (más nueva) fila ORDENAR POR [CloseDate]; ABRIR SD; — Obtener la primera fila FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey; — Iniciar el ciclo WHILE @@FETCH_STATUS = 0 BEGIN — Actualizar todas las filas según cada tienda según el estado L4L y las fechas respectivas, según las fechas de los años anteriores ACTUALIZAR [#tmp_Stores_Months]
COLOCAR [OpenDate] = @FechaAbierta,[CloseDate] = @FechaCerrada,[L4LKey] = CASO @L4LKey CUANDO 2 ENTONCES IIF(@OpenDate >= [FirstDayOfMonthPY]@L4LKey, NULL) CUANDO 3 ENTONCES IIF(@CloseDate <= [LastDayOfMonthPY]@L4LKey, NULL) CUANDO 4 ENTONCES IIF(@OpenDate >= [FirstDayOfMonthPY] Y @CloseDate <= [LastDayOfMonthPY]@L4LKey, NULL) ELSE 1 TERMINA DONDE [L4LKey] ES NULO Y [StoreKey] = @ClaveDeTienda; -- Actualización basada en el mismo mes para el cálculo del PY ACTUALIZAR [#tmp_Stores_Months]
COLOCAR [OpenDate] = @FechaAbierta,[CloseDate] = @FechaCerrada,[L4LKey_PY] = CASO @L4LKey CUANDO 2 ENTONCES IIF(@OpenDate >= [FirstDayOfMonth]@L4LKey, NULL) CUANDO 3 ENTONCES IIF(@CloseDate <= [LastDayOfMonth]@L4LKey, NULL) CUANDO 4 ENTONCES IIF(@OpenDate >= [FirstDayOfMonth] Y @CloseDate <= [LastDayOfMonth]@L4LKey, NULL) ELSE 1 TERMINA DONDE [L4LKey_PY] ES NULO Y [StoreKey] = @ClaveDeTienda; -- Obtenga la siguiente fila hasta que se procesen todas las filas FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey; FIN - Cierra el cursor CLOSE sd; DEASIGNAR sd; -- Actualice L4LKey y L4LKey_PY en todas las filas vacías ACTUALIZAR #tmp_Stores_Months SET [L4LKey] = 1 DONDE [L4LKey] ES NULO; ACTUALIZACIÓN #tmp_Stores_Months CONJUNTO [L4LKey_PY] = 1 DONDE [L4LKey_PY] ES NULO;
El resultado del procedimiento es una tabla que contiene una columna que asigna los estados L4L según el año anterior para cada mes (L4LKey) y una columna que asigna los estados L4L según el mismo año para cada mes (L4LKey_PY):
El siguiente paso es importar el resultado de este procedimiento en Power BI y agregar una relación adicional entre la tabla Bridge_4L y DIM_L4L para la nueva columna L4LKey_PY:
Esto me permite controlar el cálculo del resultado PY.
Ventas minoristas (PY) = CALCULAR([Retail Sales]
‘Inteligencia del tiempo'[Time Measures] = “PY”, USERELATIONSHIP(‘Bridge_L4L'[L4LKey_PY]’DIM_L4L'[L4LKey]) )
Ahora, los resultados son los esperados.
Aquí, el primer caso:
Y aquí están los resultados para el segundo caso:
Como puede ver, los valores PY se asignan al mismo estado L4L que los resultados del año actual.
Ahora, el usuario ve resultados consistentes, que son mucho más fáciles de entender.
Conclusión
La llamada adicional de la función USERELATIONSHIP() se puede colocar en un elemento de cálculo y ser utilizada por todas las medidas PY.
Esto hace que sea muy fácil de usar sin ninguna lógica DAX adicional.
De todos modos, este desafío fue relativamente fácil de resolver. Pero cuando consideré un cálculo mes a mes con la funcionalidad L4L, me di cuenta de que no sería posible sin algún código DAX. Posiblemente profundice en esto en un artículo futuro.
Pero este caso enfatiza la necesidad de utilizar la perspectiva del usuario al diseñar y probar una solución.
No basta con utilizar una perspectiva técnica; La perspectiva del usuario es mucho más importante a la hora de evaluar la funcionalidad y los resultados de la solución.
Para mí fue una experiencia muy interesante y muy útil para mi futuro laboral.
Espero que encuentres interesante mi enfoque. Estén atentos a mi próxima pieza.
Referencias
Este es mi artículo anterior sobre este tema:
Aquí está el artículo de SQLBI sobre el patrón comparable con una solución DAX basada en UDF independientes del modelo.
Como en mis artículos anteriores, utilizo el conjunto de datos de muestra de Contoso. Puede descargar el conjunto de datos ContosoRetailDW de forma gratuita desde Microsoft aquí.
Los datos de Contoso se pueden utilizar libremente bajo la licencia MIT, como se describe en este documento. Actualicé el conjunto de datos para cambiar los datos a fechas contemporáneas y eliminé todas las tablas que no eran necesarias para este ejemplo.