Rolling Monthly Average

[TimmK]

I want to calculate the rolling average of the last 12 months while the current month should be excluded.

There should be two variants:

• Variant 1: Divisor is 12
• Variant 2: From the last 12 months (excluding current month) the number of months where consumption is greater than 0 should be used as the divisor

In addition, I want to create a bar chart that shows the last 12 months (+ current month) WITH months where consumption is 0.

The problem is that in months without consumption the value is empty.

Bar Chart 1 is what I get (months with no consumption are missing).

Bar Chart 2 within the yellow rectangle is what I want (within last 12 months + current month; showing also months with no consumption; but it should not show months of the future or prior last 12 months)

Example table

Table I want to have in my report view

Bar Chart 1

Bar Chart 2

[amitchandak]

@TimmK , the expected output is not very clear.

With help from date table a formula like this example

Rolling 12 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))