How to correctly add rolling 12 months

[joshl525]

Hello, team, 

I was trying to add the last 12 months total value for the "Valor" Column using the following formula: 

CALCULATE(
SUM('BD_Core'[Valor]), 'BD_Core'[Versión] = "VR",
FILTER(
ALL('BD_Core'[Monthyear]),
'BD_Core'[Monthyear] <= TODAY() && 'BD_Core'[Monthyear] >= TODAY() -365
))

So, for instance, in February 2025 it should display the total amount of Feb 2024 to Jan 2025. 

However, the only thing it does is to display only the same value of the column, not adding the last 12 months for the month selected. Any help on this? 

Thanks in advance. 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please try to use KEEPFILTERS DAX function in the measure.

Please try to write something like below, and please check whether it suits your requirement.

KEEPFILTERS, functie (DAX) - DAX | Microsoft Learn

CALCULATE (
    SUM ( 'BD_Core'[Valor] ),
    'BD_Core'[Versión] = "VR",
    KEEPFILTERS (
        FILTER (
            ALL ( 'BD_Core'[Monthyear] ),
            'BD_Core'[Monthyear] <= TODAY ()
                && 'BD_Core'[Monthyear]
                    >= TODAY () - 365
        )
    )
)

[MasonMA]

@joshl525 

Hi, i'm not sure if your [Monthyear] can be used to compare directly to TODAY(). I'd suggest using a separate, dedicated Date table with a relationship to BD_Core. Then you can use DATESINPERIOD or DATEADD for rolling 12 months.

Create a proper Date table and mark it as Date table under Modelling tab. Connect it to your 'BD_Core' with one-to-many, then use this sample DAX for rolling 12 months

Measure =
CALCULATE (
    SUM ( 'BD_Core'[Valor] ),
    'BD_Core'[Versión] = "VR",
    DATESINPERIOD (
        'Date'[Date],           
        MAX ( 'Date'[Date] ),        -- current context date
        -12, MONTH                   -- go back 12 months
    )
)