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. 

[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
    )
)

[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
        )
    )
)

[v-venuppu]

Hi @joshl525 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Jihwan_Kim @MasonMA for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[joshl525]

Hello. For time being, the only thing the provided solutions do is to sum the values according to this condition: 'BD_Core'[Versión] = "VR". Hence, is not adding up the values 12 months backwards. 

[v-venuppu · Accepted solution]

Hi @joshl525 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Jihwan_Kim @MasonMA for the prompt response.

I have created a PBIX file using sample data.Please go through the attached PBIX for your reference.

[joshl525]

Thank you!

[v-venuppu]

Hi @joshl525 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @joshl525 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.