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.