Rolling 12 months calculation

[Anonymous]

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

[v-yuta-msft]

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]

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

[Anonymous]

Thank you. So the part "your_aggregation_here" is the variable name that I would create right?