En este escenario partimos de una tabla en la que se incluye información de los cambios de una cierta métrica por día. Por ejemplo, supongamos que se trata de las variaciones en la cantidad de cierto producto que hay en nuestro almacén. En este ejemplo la tabla se llama data y los dos campos que contiene son Fecha y Movimientos:
Obsérvese que la tabla incluye el último día del año 2019 aun cuando en este escenario queremos trabajar únicamente con el año 2020. Explicaré más adelante el motivo de comenzar la tabla por esta fecha.
El objetivo es calcular el estado de nuestro almacén a comienzo de cada mes (o de cada trimestre, o de cada año).
Para esto, tras leer los datos desde Power BI vamos a calcular una medida base que simplemente sume el campo Movimientos (para el contexto en el que nos encontremos en cada momento):
Quantity = SUM(data[Movimientos])
Hacemos esto para evitar tener que sumar dicho campo cada vez que necesitemos realizar este cálculo en futuras medidas.
Ahora podemos calcular el total acumulado de nuestros movimientos. Para esto vamos a crear una medida que sume Quantity desde el comienzo de los tiempos hasta "la fecha actual" (hasta la fecha máxima de cada contexto de cálculo). Todas las funciones DAX de inteligencia de tiempo requieren un calendario que sirva de referencia. Si tenemos una tabla con este calendario, estupendo. Si no, podemos crear una tabla calculada con la función CALENDARAUTO. Supongamos que llamamos a esta nueva tabla Calendar:
Calendar = CALENDARAUTO()
Una vez creada esta tabla no debemos olvidar crear una relación entre el recién creado campo Calendar[Date] y el campo Data[Fecha].
La medida que calcula el acumulado de nuestra medida Quantity (acumulado "hasta la fecha actual") es la siguiente:
Accumulated Quantity =
CALCULATE(
[Quantity],
DATESBETWEEN(
'Calendar'[Date],
FIRSTDATE(ALL('Calendar'[Date])),
LASTDATE('Calendar'[Date])
)
)
Básicamente estamos recalculando la medida Quantity (para lo que usamos la función CALCULATE) en un nuevo contexto: aquel formado por todas las fechas entre la primera que encontremos en el calendario (usando la función FIRSTDATE) y la última fecha "del contexto actual" (usando la función LASTDATE). Obsérvese el uso de la función ALL como argumento de FIRSTDATE: es lo que nos permite asegurar que estamos extrayendo el primer día de todo el calendario, y no el primer día del contexto actual.
Podemos crear una tabla en el lienzo de Power BI para ver el resultado hasta ahora:
A la tabla anterior se ha llevado el campo Calendar[Date] y las medidas Quantity y Accumulated Quantity, y, tal y como se muestra en la imagen, se ha filtrado de forma que solo muestre fechas a partir del 1 de enero de 2020.
Por último, no tenemos más que calcular, para cada fecha, el valor de la medida con el acumulado el primer día del mes correspondiente. Para esto podemos usar la función OPENINGBALANCEMONTH (o las funciones equivalentes OPENINGBALANCEQUARTER y OPENINGBALANCEYEAR si quisiéramos calcular dicho acumulado el primer día de cada trimestre o de cada año):
Quantity First Day of Month =
OPENINGBALANCEMONTH(
[Accumulated Quantity],
'Calendar'[Date]
)
Con esta medida estamos asociando a cada fecha (a cada contexto temporal, en realidad, sea de un día o no) la cifra de Accumulated Quantity del primer día del mes al que pertenece dicha fecha. Si llevamos esta medida a nuestra tabla obtenemos el siguiente resultado:
En la imagen anterior (que solo muestra la tabla entre el 1 de enero y el 5 de febrero) vemos que a cualquier día del mes de enero se asocia el valor 0 (acumulado a principio de dicho mes) y que a cualquier día de febrero se asocia el valor 21 (acumulado a principio de dicho mes). Y éste es el motivo de haber comenzado nuestra tabla de movimientos por el día 31 de diciembre de 2019: la función OPENINGBALANCEMONTH devuelve, en realidad, el valor de la métrica de que se trate antes del primer día del mes correspondiente -lo que tiene mucha lógica-: si el primer día de mi tabla (supongamos que es el 1 de enero) tengo un incremento de +2 (por ejemplo), la cifra acumulada a principio de dicho mes era de 0 (la cifra correspondiente al final del 31 de diciembre). De igual forma, si a finales de enero el total acumulado era de 21, con independencia del incremento que se produzca el 1 de febrero, el total acumulado a principios de febrero era de 21 (es decir, la cifra del último día de enero).
Si no hubiésemos incluido la fecha del 31 de diciembre, todos los días de enero habrían recibido como total acumulado a principio de mes el valor 127, que es la suma total de la columna Quantity, sencillamente porque no habría un día del mes anterior para hacer el cálculo y DAX, por diseño, consideraría toda la tabla.
