Alternar períodos de tiempo superpuestos usando la tabla de calendario

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: Intermedio.

Se me pasó una gran pregunta la semana pasada mientras estaba en la Cumbre de aplicaciones empresariales de Microsoft. El problema presentado por R Marketing (un R Marketing diferente, no yo) era que ya tenía alrededor de 200 medidas escritas, pero quería poder alternar las páginas del informe entre los datos YTD y el año completo. Por el bien de este artículo, usaré el año hasta la fecha (YTD) y el total anual móvil (MAT) como los 2 eventos alternativos.

¿Por qué no utilizar una medida de interruptor?

De acuerdo, ¿por qué no usar una medida de cambio para resolver este problema que te oigo decir? Bueno, el problema es que ya hay 200 medidas que están funcionando bien. Usar el enfoque de medida de interruptor significaría tener que escribir nuevas medidas para TODO lo que necesita para reaccionar al slicer de medida de interruptor. En cambio, R Marketing (el otro R Marketing) tuvo una idea para filtrar la tabla del calendario en función de la selección de la cortadora, y tener ese impacto en TODAS las medidas existentes – ¡gran idea R Marketing!

Pero hay un problema con este enfoque. Normalmente una cortadora (en este caso una cortadora para alternar el filtro en la tabla del calendario) tendría una sola columna con una lista de opciones de selección mutuamente excluyentes y colectivamente exhaustivas (MECE). Como puede ver en la imagen a continuación, los meses resaltados en verde deben usarse tanto en YTD como en MAT. Entonces no hay forma de usar una sola columna en la tabla Calendario para resolver este.

image_thumb-20-9654037

Entonces, ¿qué tal 2 columnas en el calendario?

Sí, eso funcionaría. Puede crear 2 nuevas columnas en la tabla de calendario, una para una bandera YTD y otra para una bandera MAT, eso funcionaría. Pero, por supuesto, el usuario necesitaría saber que tenía que seleccionar «Sí» en una y «No» en la otra cortadora, lo que no es una buena experiencia. ¿Entonces lo que hay que hacer?

¡Piense en el problema!

Si piensa en el problema anterior, cada fecha (mes en este caso) puede participar en ambos escenarios (YTD y MAT). Eso suena como un problema de relación de 1 a muchos. Entonces, una vez que comprenda eso, debería ser obvio que necesitará una tabla de datos que contenga los registros mensuales que pertenecen a cada caso de uso. Los meses que se encuentran en ambos escenarios se duplicarán en la nueva tabla de datos. La mejor forma de explicarlo es mostrando el modelo de datos final.

He creado una tabla puente llamada AllMonths. Esto contiene una lista distinta de todos los meses en la tabla del calendario en el formato YYMM.

image_thumb-21-4218229

También creé una tabla TimePeriod que contiene la lista de meses que se asignan a YTD y también a MAT. Los datos de la tabla TimePeriod se muestran a continuación y se unen a la tabla AllMonths con un filtro bidireccional como se muestra arriba. Cada mes puede participar en las asignaciones de ambos períodos y, por lo tanto, técnicamente es una tabla de datos. El filtro bidireccional significa que cualquier cortador de esta tabla empujará el filtro a la tabla Todos los meses. La tabla Todos los meses filtrará automáticamente la tabla Calendario.

image_thumb-22-4882584

Como puede ver con los datos de la tabla TimePeriod anterior (datos de muestra), el Total anual móvil cubre los períodos de julio de 2003 a junio de 2004, y el período YTD es de enero de 2004 a junio de 2004. En otras palabras, todos los períodos YTD son también incluido en el MAT. Estoy simulando los datos de muestra que muestran que el período actual es el final de junio de 2004. En la vida real, usaría algún tipo de tabla calculada para actualizar automáticamente al actualizar para mantener esta tabla actualizada para el período actual.

El resultado final

El resultado neto es el siguiente. El divisor de períodos que se muestra a continuación proviene de la tabla TimePeriod. Siempre que seleccione uno de los períodos en esta segmentación, TODAS las medidas en todo el modelo de datos se actualizarán para reflejar ese período de tiempo sin tener que escribir medidas personalizadas que reaccionen a la segmentación.

period-slicer-4457355

Cómo hacer que la tabla de períodos sea automática

Hubo una buena pregunta en los comentarios de Uriel sobre «cómo» la tabla de períodos se actualizaría automáticamente. No proporcioné una solución originalmente, ya que R Marketing dijo que estaba funcionando para él. Jugué con algo de DAX y se me ocurrió la siguiente tabla de fórmula DAX.

TestTable = 
    VAR datetoday = date(2004,7,5)  //replace this with TODAY() in real life
    VAR CompletedMonthID = CALCULATE(SELECTEDVALUE('Calendar'[MonthID]),'Calendar'[Date]=datetoday) -1
    VAR MATfrom = CompletedMonthID - 11
    VAR YTDStartDate = CALCULATE(STARTOFYEAR('Calendar'[Date]),'Calendar'[Date]=datetoday)
    VAR YTDMonthID = CALCULATE(SELECTEDVALUE('Calendar'[MonthID]),'Calendar'[Date]=YTDStartDate)
    VAR MATTable = ADDCOLUMNS(GENERATESERIES(MATfrom,CompletedMonthID),"Period","MAT")
    VAR YTDTable = ADDCOLUMNS(GENERATESERIES(YTDMonthID,CompletedMonthID),"Period","YTD")
    RETURN UNION(MATTable,YTDTable)

Este código generará una tabla de períodos que debe mantenerse actualizada cada vez que se actualizan los datos. He usado una columna MonthID en la tabla de calendario en lugar de la columna YYMM, por lo que eso significaría que la tabla puente debería cambiarse para usarla también. Aquí está mi workbook. Tabla Calendario de filtros de Adventure Works

El enfoque anterior es relativamente complejo. Probablemente sería un poco más fácil agregar 2 nuevas columnas calculadas para MAT y YTD. Estos serían relativamente fáciles de escribir usando declaraciones IF para determinar qué fechas califican para cada una. Una vez que estén en la tabla del calendario, nuevamente sería relativamente fácil crear una lista completa de todas las fechas como lo hice anteriormente. Algo como esto, supongo.

TestTable2 =
    VAR MATTable = FILTER(ALL(Calendar[YYMM],Calendar[MAT]),Calendar[MAT]="MAT"))
    VAR YTDTable = FILTER(ALL(Calendar[YYMM],Calendar[YTD]),Calendar[YTD]="YTD"))
    RETURN UNION(MATTable,YTDTable)

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.