Total Anual Móvil con PowerPivot - versión mejorada

En el post anterior mostré como calcular el TAM (Total Anual Móvil) con PowerPivot. La solución tiene algunos problemas:

1. no muestra los totales para cada año;
2. el cálculo del TAM promedio es inexacto para el primer año (2015 en nuestro ejemplo).

Aparentemente podemos solucionar el primer problema con facilidad; sencillamente agregamos los subtotales para el campo Year (el campo que arrastramos al área de las filas), pero entonces nos topamos con un nuevo problema

La tabla dinámica muestra los subtotales para todos los campos de valores y el total anual de TAM no tiene ningún sentido. Si estuviéramos trabajando sólo con tablas dinámicas no tendríamos como solucionar este problema (excepto con trucos de dudosa moralidad tales como cambiar el color de la fuente). Pero DAX nos permite solucionar este problema. Creamos la medida [TAM mejorado] de esta manera

=IF(HASONEVALUE('Calendar'[Month Number]),

                                       [TAM],

                                       BLANK()

                                       )

Cuando agregamos esta medida a la tabla dinámica veremos

La expresión HASONEVALUE('Calendar'[Month Number]) evalúa si la fila en la tabla es un total de mes; en caso afirmativo muestra la medida [TAM]; en caso negativo usa la función BLANK(). De esa manera, cuando se trata de un total del campo Year el valor mostrado es blanco (vacío).

Es hora de enfrentarnos con el segundo problema, ¿cómo calcular el TAM promedio en función de la cantidad de meses incluidos en el cálculo?.
Para hacerlo vamos a crear una medida que nos dé el número de meses en cada total de TAM. Nuestra medida debe hacer lo siguiente:

1. si el año es 2015, mostrar el número de mes (esto coincide con ell número de meses tomados en cuenta en el cálculo del TAM);
2. si no es 2015, mostrar 12.

Para facilitar la tarea vamos a crear dos medidas. La primera, [Meses_temp], da el número de mes que esta siendo evaluado en la fila de la tabla dinámica

[Meses_temp] = IF(HASONEVALUE('Calendar'[Month Number]),

                                VALUES('Calendar'[Month Number]),

                                 BLANK()

                                 )

Nuevamente usamos HASONEVALUE para evaluar si la fila es un subtotal del mes; en caso afirmativo usamos VALUES('Calendar'[Month Number] para mostrar el número de mes; en caso contrario un valor en blanco.

Agregamos la medida a la tabla dinámica para comprobar si el cálculo es correcto

Vemos que la medida funciona correctamente así que la quitamos de la tabla.

Ahora creamos otra medida [Meses_calc] que sencillamente evalúa si el año de la fila es 2015 o no; en caso de ser 2015, muestra el resultado de la medida [Meses_temp]; en caso contrario da el resultado 12

[Meses_calc] = IF(HASONEVALUE('Calendar'[Year]),

                              IF(VALUES('Calendar'[Year])=2015,

                                    [Meses_temp],

                                     12)

                                 )

Como puede apreciarse, usamos una medida dentro de otra medida. De esta manera simplificamos la sintaxis de la expresión.

Ahora creamos [TAM Promedio] con esta sencilla fórmula

[TAM Promedio]=[TAM mejorado]/[Meses_calc]

Ahora tenemos una medida útil para todos los datos, incluidos los del primer año.