Dynamic rolling previous N months

Hi YO_CO 

First of all you need to go modeling tab and choose new table and create a table that end user can select "N" for N-previous month. this table can be crarted by 

Month_number = GENERATESERIES(1,100,1)  (*100 is the maximum value you want to have for your calculation)

then you should create a column in your Date table as follows:

Year_Month = FORMAT('Date_table'[Date] , "YYYYMM")

and write another column to rank your month_date, as follows:

rank_YM = calculate(DISTINCTCOUNT('Date_table)'[Year_Month]) , filter ('Date_table' , 'Date_table'[Year_Month]>= EARLIER('Date_table'[Year_Month]) && 'Date_table'[Date] <= today()))

 

this column has blank values for future month.

 

Now, lets write a measure to calculate N_month prior, to cover this expectation you can write a measure as follows:

 

Measure N_Previous_Month := var selected_Number = selectedvalue ('month_Number' [Value] )

return

calculate (Sum(your_table [amount] ) , filter (all( your_table) , your_table [rank_YM] <= selectedvalue (Date [Rank_YM]) && your_table [rank_YM] >= selectedvalue (Date [rank_YM]) - selected_Number ))

 

** keep in mind that yor slicer should be set to single selection option

 

and also, in the table that you want to show the sum, you should selecet the dates from your Date table.

 

 

If it does not work, tell me about your tables and the relationship between them. because this can play an important role!

 

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.  

 

Hi YO_CO ,

Here is my sample data:

I don't know how your calculated Calendar column is created, so I use this instead:

Then I create a parameter for choose month numbers:

Use this DAX to create a measure and put the measure into the matrix visual:

SUM_VALUE = 
VAR _Parameter = SELECTEDVALUE(Parameter[Parameter])
VAR _CurrentMonth = MAX('Table'[Date])
VAR _PreviousMonth = EOMONTH(_CurrentMonth, -_Parameter)
RETURN
CALCULATE(
    SUM('Table'[Value]),
    ALL('Table'),
    'Table'[Date] > _PreviousMonth && 'Table'[Date] <= _CurrentMonth
)

Then use this DAX to create another measure as a filter:

Measure = 
VAR _Parameter = SELECTEDVALUE(Parameter[Parameter])
VAR _CurrentMonth = MAXX(FILTER(ALL('Table'), 'Table'[Flag] = "IsCurrentMonth"), 'Table'[Date])
VAR _PreviousMonth = EOMONTH(_CurrentMonth, -_Parameter)
RETURN
IF(
    MAX('Table'[Date]) > _PreviousMonth && MAX('Table'[Date]) <= _CurrentMonth,
    1,
    0
)

Make the settings as shown in the figure below:

Output:
If I choose 3 months:

If I choose 13 months:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you both for your detailed solutions.

Unfortunately this did not solve the issue, the months were either non-cumulative or the filters didn't work.

Using Rank_YM to rank the [Year Month] column also resulted in circular depandancy.

Eventually I managed to get it working using the mentioned video and some added transformations, though I'm not sure why the second calendar table is required.

Thanks again!