Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Rolling 12 months calculation

I would like to create a column that takes TRUE for all those values that are returned within the rolling months as of the previous month. So for example, we are in August 2018, I would need the colu...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Akshaya,

 

As a general solution, you can create a measure using this pattern:

 

VAR Previous_Month =
EDATE ( MAX ( Table[Date] ), -1 )
VARPrevious_12_Month =
EDATE ( MAX ( Table[Date] ), -13 )
RETURNResult =
CALCULATE (
    Your_Aggregation_Here,
    FILTER (
        Table,
        Table[Date] >= Previous_Month
            && Table[Date] <= Previous_12_Month
    )
)

Regards,

Jimmy Tao

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    Thank you. So the part "your_aggregation_here" is the variable name that I would create right?
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey,

     

    I'm not quite sure as to what would go under "Your_Aggregation_Here". Also, would this result in a TRUE or FALSE value?

     

    I would want the Flag column that I am creating to look like this:

     

    Flag   Year     Month  Day

    0        2017    Jan        15

    0        2017    Jan         16

    .

    .

    .

    1       2017    July        1

    1       2017    July         2

    .

    .

    .

    1      2018      July         31

     

     

    Similarly, once we go to August 2018, Flag should be 1 from August 1, 2017 - August 31, 2018