Dynamic rolling previous N months

[YO_CO]

Hi all,

I'm looking for a way to show the rolling N months sum, and have the matrix table respond dynamically.

For example if I choose Aug 2024 and 3 months, the matrix should only show JUN, JUL, AUG and a calculated 3 months sum for each (just to clarify - JUN should be the sum of APR + MAY + JUN).

If I choose 13 months, the matrix should show AUG 2023 till AUG 2024, with each month showing the sum of all previous 12 months + itself.

The caveat:

Page shouldn't have a date slicer. The page should always show from previous N months till current month (determined by calculated Calendar column "IsCurrentMonth".

I saw a video explaining how to do this:

https://youtu.be/JUUXGw86wUY?si=izFXfJ4lRk8Kx7-y

But no matter what I do it only adds months and shows future N months And even then it doesn't calculate the sums correctly, instead showing same value for all months. I don't need future, only past. Tried everything I could think of, but still only gets future.

Plus I'm not 100% sure the adjustment I made to rely on "Is Current Month" instead of actual dates slicer is correct.

Anyone had a similar issue and found a solution, or can suggest a different way to achieve this?

Thanks a lot!

[YO_CO]

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!

[Anonymous]

Hi @YO_CO ,

We're sorry that our ideas didn't help you solve your problem, and we're glad to hear that you finally found a solution. Could you please explain your solution so that everyone can learn from it, please?

Best Regards,
Dino Tao

[Anonymous]

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.

[Selva-Salimi]

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.