Cálculo de totales acumulados

Un escenario frecuente es aquel en el que queremos contabilizar el total acumulado, incluyendo todos los datos, no solo los implicados en el contexto. Por ejemplo, supongamos que deseamos calcular las ventas acumuladas en una cierta fecha o en un cierto período. Partamos de una tabla con el detalle de las ventas. Definimos la medida

Ventas = SUM(FactSales[SalesAmount])

Lógicamente podemos utilizar esta medida para mostrar el total de ventas simplemente llevándola a una visualización tipo "card":

Escenario Cálculo de totales acumulados

Pero no nos permite averiguar las ventas acumuladas hasta el período implicado en el contexto:

Escenario Cálculo de totales acumulados

...pues va a calcularse únicamente para los registros de dicho contexto. Así, vemos en el ejemplo anterior que la cifra de 2009 es simplemente las ventas de dicho año, no el acumulado hasta entonces que debería coincidir con el total.

El método a seguir sería el cálculo desde la primera fecha disponible hasta el contexto actual de la medida en cuestión. Es decir:

Ventas acumuladas = CALCULATE(
    [Ventas];
    Periodo entre Primera_fecha_disponible y Última_fecha_del_contexto_actual
)

El período en cuestión puede calcularse usando la función DATESBETWEEN que devuelve el conjunto de fechas entre dos dadas. Tendríamos, por lo tanto, que codificar la siguiente medida:

Ventas acumuladas = CALCULATE(
    [Ventas];
    DATESBETWEEN(
        DimDate[Datekey];
        Primera_fecha_disponible;
        Última_fecha_del_contexto_actual
    )
)

(suponiendo que la tabla calendario fuese DimDate). Ahora bien, es el cálculo de esas dos fechas lo que resulta más complejo, pues queremos que la primera fecha ("Primera_fecha_disponible") sea absoluta y haga referencia a la primera fecha disponible en el calendario, mientras que la segunda ("Última_fecha_del_contexto_actual") queremos que se adapte al contexto.

Así, vamos a extraer la primera fecha disponible en el calendario usando la función FIRSTDATE, que devuelve la primera fecha del contexto actual dada una columna de fechas.Su sintaxis es

FIRSTDATE(<fechas>)

Esta función considera la columna indicada, le aplica el filtro impuesto por el contexto y devuelve la primera fecha del conjunto resultante. ¿Cómo podemos forzar que la función considerre todo el calendario? Sobrescribiendo el contexto utilizando la función ALL. De esta manera:

FIRSTDATE(ALL(DimDate[Datekey]))

...nos devuelve la primera fecha incluida en el calendario con independencia del contexto.

Para calcular la última fecha del contexto actual no necesitamos nada especial pues, de hecho, queremos que se adapte al contexto, de forma que:

LASTDATE(DimDate[Datekey])

...nos devolverá la fecha adecuada.

Es decir, la función

DATESBETWEEN(
        DimDate[Datekey];
        FIRSTDATE(ALL(DimDate[Datekey]));
        LASTDATE(DimDate[Datekey])
)

...va a devolver, tal y como queríamos, el conjunto de fechas entre la primera existente en el calendario y la última implicada en el contexto actual. Podemos escribir a continuación la versión definitiva de la medida:

Ventas acumuladas = CALCULATE(
    [Ventas];
    DATESBETWEEN(
        DimDate[Datekey];
        FIRSTDATE(ALL(DimDate[Datekey]));
        LASTDATE(DimDate[Datekey])
    )
)

...y añadir esta medida a la tabla original de ventas:

Escenario Cálculo de totales acumulados

Ahora sí podemos ver cómo el resultado de la medida incluye el total acumulado hasta el contexto actual. De hecho, si llevamos a una matriz las ventas por mes del año 2008, por ejemplo, obtendríamos el siguiente resultado:

Escenario Cálculo de totales acumulados

Podemos ver cómo las ventas acumuladas de enero (3.328.363.312,41€) coinciden con el acumulado de 2007 (3.144.393.292,13€) más las ventas de enero de 2008 (183.970.020,28€).