¿Qué es Like-for-Like (L4L)?
para garantizar que sólo se comparen elementos comparables.
Los elementos pueden ser productos, tiendas, grupos de clientes, etc.
Aquí puedes leer una buena explicación de este tema.
En el caso actual, crearé una solución para tiendas.
Las tiendas pueden abrir, cerrar o incluso cerrar temporalmente por renovaciones, reparaciones u otros motivos.
Por tanto, las tiendas pueden ser comparables o no comparables al comparar los resultados actuales con los del año anterior. Esto significa que cuando una tienda no estuvo activa durante un período específico en el año anterior, no es comparable en el año actual, cuando estuvo activa durante el mismo período.
L4L garantizará que un usuario del informe pueda seleccionar si desea incluir o excluir tiendas no comparables.
Para seleccionar el estado L4L, creo una tabla DIM_L4L:
Puedo usar las columnas L4L_Test y Reason como jerarquía en un Slicer o en un objeto visual Matrix.
Las tiendas
Elegí algunas tiendas del conjunto de datos de ContosoRetailDW (detalles del conjunto de datos de ContosoRetailDW en la sección Referencias a continuación).
En este caso elegí las tiendas de Italia.
Aquí está la lista de tiendas italianas con las fechas de apertura y cierre y los estados L4L asignados:
En esta tabla, agregué dos columnas con las fechas de apertura y cierre de fin de mes para cada tienda.
Esta tabla contiene todas las tiendas que no son comparables.
Como puede ver, las tiendas 224 y 226 tienen fecha de apertura en 2024, 222 tiene fecha de cierre en 2024, y 222 y 225 cerraron temporalmente en 2023 y 2024.
Todas las demás tiendas se configurarán en comparables (L4LKey = 1) durante la preparación de datos para la solución.
Que estar atento
Entonces, ¿cuáles son los requisitos?
Siempre recordamos el año anterior. En 2025, miramos al 2024, y en 2024, miramos al 2023. El usuario debe poder seleccionar cada uno de los estados L4L. Cuando no se selecciona ningún estado, los datos no se filtran y se muestran todas las tiendas. Queremos controlar los resultados por mes. No es necesario cambiar los resultados diarios. Cuando una tienda cambia de estado de 1 (Comparable) a otro en el año anterior, se deben filtrar los datos en el año en curso.
Por ejemplo, una tienda abre en agosto de 2024. Cuando miramos solo los datos comparables para 2025, no deberíamos ver ningún resultado de enero a julio de 2025. Las medidas utilizadas en los informes no deberían cambiarse para reflejar los resultados necesarios.
Preparando los datos
Primero, debo crear una tabla que contenga todos los meses. Además, debe incluir la primera y la última fecha de cada mes tanto del año actual como del anterior.
Para ello, creo una tabla como referencia a partir de la tabla Fecha en Power Query.
Mantengo solo las siguientes columnas y elimino todas las demás:
Clave del mes Clave del mes PY Primer día del mes Último día del mes Primer día del mes PY Último día del mes PY
Después de eso, elimino todos los duplicados.
La tabla L4L_Months tiene este aspecto:
A continuación, construí la solución en Power Query combinando las tablas Tienda, L4L_Months y la tabla con las Tiendas y las fechas de apertura y cierre (Nombre de la tabla: L4L_Dates).
Creación de la solución Power Query
Creé una tabla referenciada desde la tabla “Tienda” y le cambié el nombre a “Bridge_L4L”.
Elimino todas las columnas, excepto la columna StoreKey.
A continuación, necesito una fila para cada Tienda y cada mes.
Para esto, agrego una columna para la tabla L4L_Months:
Cuando amplío todas las columnas de la tabla L4L_Month, obtengo una tabla con una fila para cada combinación de tienda y mes:
Ahora, cada tienda aparece varias veces en la lista. Para tener un valor-clave único para cada tienda, agrego una columna StoreMonthKey:
A continuación preparo la tabla con los datos de la tienda llamada “L4L_Dates”.
En cuanto a la tabla Bridge_L4L, agregué la tabla L4L_Months a la tabla de tiendas, que contiene las fechas de apertura y cierre (Ver Figura 2).
Nuevamente, amplío todas las columnas de la tabla L4L_Months, como antes.
Nuevamente, cada tienda aparece varias veces en la lista. Agrego el mismo valor-clave único para cada tienda (StoreMonthKey):
Texto.De([StoreKey]) & “_” & Texto.De([MonthKey])
En este punto, tengo toda la información necesaria para seleccionar las filas con el estado L4L correcto.
Debo hacerlo de acuerdo con las fechas de apertura y cierre y compararlas con las columnas First- y LastDateOfMonthPY usando la lógica necesaria por estado L4L.
Para esto, agrego una columna personalizada con la siguiente expresión:
si [L4LKey] = 2 y
[OpenDate] >= [FirstDayOfMonthPY]
entonces es cierto si no [L4LKey] = 3 y
[CloseDate] <= [LastDayOfMonthPY]
entonces es cierto si no [L4LKey] = 4 y ([OpenDate] >= [FirstDayOfMonthPY] y [CloseDate] <= [LastDayOfMonthPY]) entonces verdadero o falso
Llamo a esta columna “Válida”, ya que marca las filas correctas para cada estado L4L.
A continuación, filtro los datos para conservar solo las filas válidas:
El siguiente paso es fusionar la tabla Bridge_L4L con la tabla L4L_Dates utilizando las columnas StoreMonthKey creadas anteriormente:
En este punto, solo necesito la columna L4LKey de L4L_Dates en la tabla Bridge_L4L:
La mayoría de las filas contienen un valor nulo en la columna L4LKey.
Todas estas filas son para tiendas y meses comparables.
Por esta razón, reemplazo todos los valores nulos por 1:
Por último, eliminé todas las columnas excepto las necesarias:
Con estos pasos, creé la tabla Bridge_L4L, que puede servir como filtro según el estado L4L seleccionado.
¿Qué queda por hacer en Power BI?
Ahora debo colocar la nueva tabla Bridge_L4L entre las tablas Store y la Fact-Table “Retail Sales”.
Luego puedo agregar una relación del nuevo DIM_L4L a la tabla Bridge_L4L.
Pero para agregar una relación de la tabla Bridge_L4L a la tabla de hechos Ventas minoristas, debo agregar la misma StoreMonthKey a la tabla Ventas minoristas para identificar de forma única la tienda para cada mes.
Hago esto en la consulta SQL para recuperar los datos de hechos:
SELECCIONAR [F].[SaleLineCounter] COMO [Sale Line Counter]
,CONVERTIR(fecha, FECHAADD(aaaa, 16, [F].[DateKey])) COMO [DateKey]
,[F].[channelKey]
,[F].[StoreKey]
,CONCAT(CONVERTIR(nvarchar(25), [F].[StoreKey]) ,’_’ ,CONVERTIR(nvarchar(25), AÑO(CONVERTIR(fecha, FECHAADD(aaaa, 16, [F].[DateKey])))) ,DERECHA(’00’ + CONVERTIR(nvarchar(25), MES(CONVERTIR(fecha, FECHAADD(aaaa, 16, [F].[DateKey])))), 2) ) COMO [StoreMonthKey]
,[F].[ProductKey]
,[F].[PromotionKey]
,[F].[CurrencyKey]
,[F].[UnitCost]
,[F].[UnitPrice]
,[F].[SalesQuantity]
,[F].[ReturnQuantity]
,[F].[ReturnAmount]
,[F].[DiscountQuantity]
,[F].[DiscountAmount]
,[F].[TotalCost]
,[F].[SalesAmount]
,[F].[DateKeyYear]
DE [dbo].[v_FactSales] COMO [F];
Ahora obtengo esta columna en la tabla de hechos:
Después de todo esto, el modelo de datos para las tablas involucradas es el siguiente:
Como puede ver, solo tengo relaciones unidireccionales de uno a muchos, como debería ser.
Los resultados
Después de agregar una matriz visual al informe con la jerarquía L4L, las tiendas y los meses en las columnas, obtengo esto para el monto de ventas para 2025:
Veamos los diferentes escenarios:
Apertura de tiendas Florencia y Milán:
Sus fechas de apertura fueron en mayo y octubre de 2024. Como estos meses no contienen las ventas del mes completo, se consideran no comparables. Como puede ver, las Ventas cambian entre los estados No Comparable – Apertura y Comparable. Cierre de Tienda Contoso Roma:
La misma imagen aquí. La tienda de Roma cerró en agosto de 2024. Cualquier resultado posterior a ese mes es visible como comparable. Recuerda que estos son datos de demostración y no habrá Rebajas de noviembre y diciembre en el mundo real. Pero puede haber costos asignados a la Tienda si desea analizarlos, por ejemplo, en un informe de P&L. Tienda refrescante Contoso Torino
Esta tienda cerró entre marzo y julio de 2024. Por lo tanto, las Ventas durante estos meses deben considerarse como No Comparables.
Incluso si miramos el año 2024, vemos que la tienda de Roma está marcada correctamente como Refresh y todas las demás tiendas son comparables, excepto las tiendas de Florencia y Milán:
Los resultados son exactamente los que esperaba.
Recuerde que trabajo con datos de demostración e intencionalmente no eliminé los datos de las tiendas cerradas. De esta manera, los resultados son mejor visibles.
Como hacerlo diferente
Este enfoque funciona, pero hay otras formas de hacerlo. Depende de los requisitos qué enfoque se adapta a su situación.
Puede mover esta lógica de Power Query al lenguaje de programación de su preferencia, como SQL o Python. Este enfoque, con la tabla puente, es excelente, ya que nos permite configurar la relación entre la tienda y la tabla puente para el filtrado bidireccional y ocultar las tiendas que no se ajustan al estado L4L seleccionado. Todas las tablas de hechos están vinculadas a la tabla Bridge para que no puedan producirse dependencias circulares. Una mejor manera podría ser integrar el estado L4L en las tablas de hechos. Esto evitaría la necesidad de tener la mesa Bridge en primer lugar. Puede decidir agregar una lógica de historización a la lógica de dimensión de la Tienda y agregarle el estado L4L. En este caso, debe incluir la jerarquía L4L en la tabla Store. Este podría ser el mejor enfoque, ya que incluiría una lógica SCD2 estándar. Al mismo tiempo, es una elección más compleja porque añade complejidad a la hora de preparar la tabla de dimensiones de la Tienda.
La elección del mejor enfoque de modelado depende de los requisitos y las habilidades que tenga.
Conclusión
Hoy les mostré cómo crear una solución comparable para comparar tiendas a lo largo de los años.
Se ha logrado el objetivo de construir una solución sin cambios en las medidas DAX. Toda la solución está totalmente basada en datos.
Este es un tema importante. Una lógica basada en DAX puede ser insostenible, ya que introduce la necesidad de incorporar lógica DAX adicional en su modelo de datos. Siempre hay que pensar en esto al añadir nuevas medidas.
Además, puede introducir problemas de rendimiento, ya que el código podría ser más complejo y potencialmente más lento de lo que sería sin él.
Soy un gran admirador de las soluciones basadas en datos. En la mayoría de los casos, son mejores que tener un código DAX complejo.
Espero que hayas aprendido algo nuevo e interesante. Nos vemos aquí pronto.
Referencias
Aquí, un vídeo de YouTube de SQLBI sobre la creación de una solución L4L para marcas:
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.