Moving Anual Total (MAT) for Inflation

[Anonymous]

Hi people!

I'm trying to calculate a Moving Anual Total for Inflation row by row and I'm really stuck with it.

The calculated column that I created so far is:

Inflacion MAT = 
VAR Periodo = 'Inflación País'[IdPeriodo]
VAR InicioMAT2 = IF(LEN(CONCATENATE(LEFT('Inflación País'[IdPeriodo];2)+1;RIGHT('Inflación País'[IdPeriodo];4)-1))=5;CONCATENATE(0;CONCATENATE(LEFT('Inflación País'[IdPeriodo];2)+1;RIGHT('Inflación País'[IdPeriodo];4)-1));CONCATENATE(LEFT('Inflación País'[IdPeriodo];2)+1;RIGHT('Inflación País'[IdPeriodo];4)-1))
VAR InicioMAT = 'Inflación País'[InicioMAT]
VAR Anio = RIGHT('Inflación País'[IdPeriodo];4)
VAR Mes = LEFT('Inflación País'[IdPeriodo];2)
VAR MesInicioMAT = FORMAT(LEFT('Inflación País'[IdPeriodo];2)+01;"00")
VAR AnioAnterior = FORMAT(RIGHT('Inflación País'[IdPeriodo];4)-01;"0000")
VAR Pais = 'Inflación País'[IdPais]
RETURN
CALCULATE(
    PRODUCT(
        'Inflación País'[Inflacion 1+MTH]
    );
    FILTER(
        'Inflación País';
        RIGHT('Inflación País'[IdPeriodo];4) = Anio &&
        LEFT('Inflación País'[IdPeriodo];2) <= Mes ||
        (RIGHT('Inflación País'[IdPeriodo];4) = AnioAnterior && LEFT('Inflación País'[IdPeriodo];2) >= MesInicioMAT) &&
        'Inflación País'[IdPais] = Pais
    )
)
- 1

There are variables that I declared but not actually using them.

And the data table is:

The calculate for the MAT Inflation should be:

The product of all the Inflacion 1+MTH where the Country (IdPais) is the same and the period (IdPeriod) is between that current period and last past 11 months. Hope I'm clear.

Any help with this will be really usefull!

Thanks in advance.

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, I notice that your period column is "Month+Year". I suggest you add a new period column like "Year+Month" and convert it into numeric type.

Then you can calculate the cumulative total based on this new period column and use ALLEXCEPT() to group on conuntry column.

Total Amount =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table'[YearMonth] ),
        'Table'[YearMonth] <= MAX ( 'Table'[YearMonth] )
            && 'Table'[YearMonth]
                > MAX ( 'Table'[YearMonth] ) - 100
    ),
    ALLEXCEPT ( 'Table', 'Table'[Country] )
)

Regards,

[Anonymous]

Thanks @v-sihou-msft for your reply.

Finally, I decided to perform this with a measure and not a calculated column. Anyway, I tested your solution and it worked fine.

Many thanks!