DAX Rolling total with Measure

[CoolArsh]

Here is my Query 

EVALUATE
SUMMARIZE (
    MoveTable,
    MoveTable[MonthYear],
    MoveTable[MontherCounter], 
    "Moves" , SUM(MoveTable[NoOfMoves]),
    "Rolling12Month" , CALCULATE(    
                    SUM(MoveTable[NoOfMoves),
                    FILTER(       MoveTable,
                                  MoveTable[MontherCounter] >= (MoveTable[MontherCounter] - 11) 
                               && MoveTable[MontherCounter] <= MoveTable[MontherCounter]  
                        )                        
                )
)

Here is the result and as you can see the Rolling12Month is not adding the last 12 months. What am I missing???

[v-piga-msft · Accepted solution]

Hi @CoolArsh,

Try the formula below.

Table =
SUMMARIZE (
    MoveTable,
    MoveTable[MonthYear],
    MoveTable[MontherCounter],
    "Moves", SUM ( MoveTable[Moves] ),
    "Rolling12Month", CALCULATE (
        SUM ( MoveTable[Moves] ),
        FILTER (
            ALL ( MoveTable ),
            'MoveTable'[MontherCounter]
                >= MAX ( 'MoveTable'[MontherCounter] ) - 12
                && 'MoveTable'[MontherCounter] <= MAX ( 'MoveTable'[MontherCounter] )
        )
    )
)

Here is the output.

Best Regards,

Cherry

[CoolArsh]

Hi @v-piga-msft I ran into an issue with this, the moment I introduce Region to this dataset its no more doing rolling total for 12 months per region. Please take a look at the the below picture.