Calculating average 12 month average

[Nicpet0]

Hi Commnity

I am seeking help to dynamically calculate average amount always for 12 months back. It needs to start from last month and then go 11 months back, meaning when currently in September, then the 12 month period should calculate average from August 2025 - September 2024. Start of next month it needs to start with September and so on. 

Also, im not sure if this is useful information but i have a fiscal year slicer in my report, but it needs to ignore the filter context on that one.

Is there anyone who can come up with a sample code on how they would write the DAX for this.

Thank you in advance

[v-tejrama]

Hi  @Nicpet0 ,

Thank you @tayloramy  and  @johnt75  for the response provided! 

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[tayloramy · Accepted solution]

Hi @johnt75, 

When a fiscal year slicer (or any date filter) is active, time-intelligence measures can be evaluated over a truncated window. That is why a “last 12 months” average can come out wrong or vary with slicers. The fix is to 1) anchor the period to the last complete month and 2) explicitly filter the calculation to the previous 12 months while removing the slicer’s date filters.

Quick solution: fixed last 12 months average (ignores FY slicer)

Assumptions: you have a proper Date table marked as a date table, with a daily [Date] column; [Amount] is your base measure (SUM, etc.).

Avg 12M (last complete month) :=
VAR Anchor = EOMONTH( TODAY(), -1 ) -- last day of the previous month
RETURN
DIVIDE(
    CALCULATE(
        [Amount],
        REMOVEFILTERS ( 'Date' ), -- ignore fiscal year/date slicers
        DATESINPERIOD ( 'Date'[Date], Anchor, -12, MONTH )
    ),
    12
)

• EOMONTH finds the last complete month boundary (docs).
• DATESINPERIOD builds exactly 12 months back from that anchor (docs).
• REMOVEFILTERS clears the Date table’s filter context so FY slicers do not interfere (docs).
• DIVIDE safely divides by 12 (docs).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[johnt75]

You could create a measure like

12 month rolling average =
VAR EndDate =
    EOMONTH ( TODAY (), -1 )
VAR StartDate =
    EOMONTH ( TODAY (), -13 ) + 1
VAR Months =
    CALCULATETABLE (
        VALUES ( 'Date'[Year Month] ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
VAR Result =
    AVERAGEX (
        Months,
        CALCULATE ( [Measure to average], REMOVEFILTERS ( 'Date'[Fiscal Year] ) )
    )
RETURN
    Result