Rolling 12M Average measure

Hi all.

I’m having some issues with this measure. In its current form, it returns a value for every date (note that the value is the same across the entire month).

The data works as follows:

• The data is stored on a monthly basis.

• However, the current month is available on a daily basis.

• Once the month is closed, all daily rows are aggregated into a single monthly row.

What I want is for the measure to return one value per month, displayed only on the last day of the month.
For example, for December, the value should appear on December 31, and all other dates in December should be blank.

For the current month, the measure should:

• Use the last closed date (i.e., the last date with headcount data).

• Treat this date as the “end-of-month” value when calculating the rolling 12-month average.

• Still display the value in the visual on the last closed date (e.g., if today is January 27, the last closed date is January 26, and the value should be shown on January 26).

Additionally, the measure should stop at the current month and never return values for future months.

Can someone please help me with this?

Thank you very much in advance.

_RollingAvg12MHC = 

VAR _Today = TODAY()
VAR _LastFullMonthEnd = EOMONTH(_Today, -1)

// Detect Calendar filtering
VAR _IsCalendarFiltered =
    COUNTROWS(ALLSELECTED('Calendar')) < COUNTROWS(ALL('Calendar'))

// Raw max date from selection (may be in the future)
VAR _SelectedMaxDate =
    IF(
        _IsCalendarFiltered,
        MAX('Calendar'[Date]),
        _LastFullMonthEnd
    )

// HARD CAP: never go beyond last closed month
VAR _MaxDate =
    MIN(_SelectedMaxDate, _LastFullMonthEnd)

VAR _MaxMonthEnd = EOMONTH(_MaxDate, 0)

VAR _MinMonthStart =
    DATE(
        YEAR(EDATE(_MaxMonthEnd, -11)),
        MONTH(EDATE(_MaxMonthEnd, -11)),
        1
    )

// Build month list (never includes future months now)
VAR _MonthStarts =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL('Calendar'),
                'Calendar'[Date] >= _MinMonthStart &&
                'Calendar'[Date] <= _MaxMonthEnd
            ),
            "MonthStart",
                DATE(
                    YEAR('Calendar'[Date]),
                    MONTH('Calendar'[Date]),
                    1
                )
        )
    )

VAR _AvgHeadcount =
    AVERAGEX(
        _MonthStarts,
        VAR _MonthStart = [MonthStart]
        VAR _MonthEnd = EOMONTH(_MonthStart, 0)
        RETURN
            CALCULATE(
                SUM('Employee headcount'[Count]),
                'Employee headcount'[EmploymentIsActiveIn1/0] = 1,
                'Employee type'[Employee type name] IN
                {   
                    "Apprentice",
                    "Blue-collar Employee",
                    "Expatriate",
                    "Indirect Blue-collar Employee",
                    "Trainee",
                    "White-collar Employee"
                },
                'Calendar'[Date] >= _MonthStart,
                'Calendar'[Date] <= _MonthEnd
            )
    )

RETURN
IF(_AvgHeadcount < 4, BLANK(), _AvgHeadcount)