Forum Discussion

CoolArsh's avatar
CoolArsh
Regular Visitor
7 years ago
Solved

DAX Rolling total with Measure

Here is my Query  EVALUATE SUMMARIZE (     MoveTable,     MoveTable[MonthYear],     MoveTable[MontherCounter],      "Moves" , SUM(MoveTable[NoOfMoves]),     "Rolling12Month" , CALCULATE(     ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

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's avatar
CoolArsh
Regular Visitor
7 years ago

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.