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 column to give a true for all those values from July 2017 to July 2018. Similarly, in September 2018, it would need to be August 17 - August 18

Need Help

3 Replies

  • 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