Calculating average 12 month average

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.