DAX Rolling total with Measure

Question

Here is my Query&nbsp;EVALUATESUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;MoveTable,&nbsp;&nbsp;&nbsp;&nbsp;MoveTable[MonthYear],&nbsp;&nbsp;&nbsp;&nbsp;MoveTable[MontherCounter],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Moves" , SUM(MoveTable[NoOfMoves]),&nbsp;&nbsp;&nbsp;&nbsp;"Rolling12Month" , CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(MoveTable[NoOfMoves),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MoveTable,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MoveTable[MontherCounter] &gt;= (MoveTable[MontherCounter] - 11)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; MoveTable[MontherCounter] &lt;= MoveTable[MontherCounter]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Here is the result and as you can see the&nbsp;Rolling12Month is not adding the last 12 months. What am I missing???&nbsp;&nbsp;&nbsp;&nbsp;

v-piga-msft · Accepted Answer

Hi&nbsp;CoolArsh,
&nbsp;
Try the formula below.
&nbsp;
Table =
SUMMARIZE (
    MoveTable,
    MoveTable[MonthYear],
    MoveTable[MontherCounter],
    "Moves", SUM ( MoveTable[Moves] ),
    "Rolling12Month", CALCULATE (
        SUM ( MoveTable[Moves] ),
        FILTER (
            ALL ( MoveTable ),
            'MoveTable'[MontherCounter]
                &gt;= MAX ( 'MoveTable'[MontherCounter] ) - 12
                &amp;&amp; 'MoveTable'[MontherCounter] &lt;= MAX ( 'MoveTable'[MontherCounter] )
        )
    )
)

Here is the output.
&nbsp;

&nbsp;
Best Regards,
Cherry

Forum Discussion

DAX Rolling total with Measure

2 Replies

Related content

% of total measure

Total of Measure

Rolling 12M Average measure

Rolling accumulation

Rolling accumulation