Rolling 12M Average measure

[faaz]

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)

[faaz · Accepted solution]

Thank you all for your replies. I was playing around and found this to give me what I needed:

_HC = 

VAR _MaxCalendarIDInData =
    CALCULATE(
        MAX('Employee headcount'[CalendarID]),
        ALL('Employee headcount')
    )
 
// Convert CalendarID back to Date for calculations
VAR _MaxDateInData =
    CALCULATE(
        MAX('Calendar'[Date]),
        FILTER(ALL('Calendar'), 'Calendar'[CalendarID] = _MaxCalendarIDInData)
    )
 
 
// 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]),
        _MaxDateInData
    )
 
// HARD CAP: never go beyond last date in Employee headcount
VAR _MaxDate =
    MIN(_SelectedMaxDate, _MaxDateInData)
 
// For rolling 12-month window
VAR _MaxDateProjectedToMonthEnd = EOMONTH(_MaxDate, 0)
VAR _MinMonthFor12Months = EDATE(_MaxDateProjectedToMonthEnd, -11)
 
// Get distinct dates that exist in Employee headcount table within 12-month window
VAR _AvailableDates =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                'Employee headcount',
                RELATED('Calendar'[Date]) >= _MinMonthFor12Months &&
                RELATED('Calendar'[Date]) <= _MaxDate &&
                'Employee headcount'[EmploymentIsTerminatedIn1/0] = 0
            ),
            "Date", RELATED('Calendar'[Date])
        )
    )
 
// Build month list from available dates only (last 12 months)
VAR _MonthStarts =
    DISTINCT(
        SELECTCOLUMNS(
            _AvailableDates,
            "MonthStart",
                DATE(
                    YEAR([Date]),
                    MONTH([Date]),
                    1
                )
        )
    )
 
VAR _AvgHC =
    AVERAGEX(
        _MonthStarts,
        VAR _MonthStart = [MonthStart]
        VAR _MonthEnd = EOMONTH(_MonthStart, 0)
        RETURN
            CALCULATE(
                SUM('Employee headcount'[Count]),
                'Employee headcount'[EmploymentIsActiveIn1/0] = 1,
                'Employee headcount'[EmploymentIsTerminatedIn1/0] = 0,
                'Employee type'[Employee type name] IN
                {   
                    "Apprentice",
                    "Blue-collar Employee",
                    "Expatriate",
                    "Indirect Blue-collar Employee",
                    "Trainee",
                    "White-collar Employee"
                },
                NOT 'Employee status'[Employee status name] IN {
                    "Reported No Show",
                    "Retired",
                    "Terminated"
                },
                'Calendar'[Date] >= _MonthStart,
                'Calendar'[Date] <= _MonthEnd
            )
    )
 
RETURN
IF(_AvgHC >= 4, _AvgHC, BLANK())

[AWD]

I might sugest using a SWITCH measure. Assumming the _AvgHeadcount measure is returning the right value, but you issue is getting it to appear in the right date something like this may work for you in your RETURN:

SWITCH (
TRUE (),
_AvgHeadcount < 4 || SELECTEDVALUE ( 'Calendar'[Date] ) > _Today, BLANK (),
SELECTEDVALUE ( 'Calendar'[Date] ) = _LastFullMonthEnd, _AvgHeadcount,
BLANK ()
)

[faaz]

This just returns the last value of the last closed month (december 2025):

[DanieleUgoCopp]

Hello,

I think you can fix it like this:

_RollingAvg12MHC =
VAR _Today = TODAY()

VAR _LastClosedDate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        NOT ISBLANK ( SUM ( 'Employee headcount'[Count] ) )
    )

VAR _CurrentDate = MAX ( 'Calendar'[Date] )

VAR _DisplayDate =
    IF (
        EOMONTH ( _CurrentDate, 0 ) > _LastClosedDate,
        _LastClosedDate,
        EOMONTH ( _CurrentDate, 0 )
    )

VAR _AnchorDate = _LastClosedDate
VAR _MaxMonthEnd = EOMONTH ( _AnchorDate, 0 )
VAR _MinMonthStart = EOMONTH ( _MaxMonthEnd, -11 ) + 1

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 (
    _CurrentDate <> _DisplayDate,
    BLANK(),
    IF ( _AvgHeadcount < 4, BLANK(), _AvgHeadcount )
)

The code now is separating calculation logic from display logic, the rolling 12 month average is always calculated using the last closed date as the anchor.
Then the measure is shown only on one date per month, the real month end for closed months and the last available data date for the current month then all other dates return blank, so the value appears only once per month and never for future dates.

If it fix your problem you can choose me as answer,  thank you

[faaz]

And also, the measure returns 0 if the measure is <4. 

[FBergamaschi]

Hi @faaz,

just to be sure to help you in the best possible way, can you please show an image of the tables and the matrix with what you are getting that is not ok? I am not sure I got the point of dates that become a single month when the month is completed (I understand the conceps but need to see it).

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[faaz]

Hi @FBergamaschi . Thank you very much. As you can see in the picture, the measure returns a valeu for all dates. I need it to return only a value for the last day of the month. And for the current month it should use the last closed date as the End of month. Does that make sense?

[FBergamaschi]

Hi @faaz,

yes indeed, it does make sense.

Try this code, if it does not work as you expect, please consider sending me the file via private message, supposing you cannot share it here, to francesco.bergamaschi@kubisco.com

Please note I used in the below code a column I am supposing you have (Calendar[yearmonth]) that should have values like 202601 for january, 202602 for february etc, if you do not have it or have trouble creatig it here is the code of the column

yearmonth = 'Calendar'[Year] & FORMAT ( Month[Date], "00" )

Here is the revised code

_RollingAvg12MHC =

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

VAR _MaxVisibleDate = MAX ( 'Calendar'[Date] )
VAR _MaxVisibleMonth = MAX ( "Calendar'[yearmonth] ) --- change this if the column has a different name, should be a column with year and month together in a single field, first four digit for the year, last two for the month
VAR _EOMMaxVisibleMonth = EOMONTH(MaxVisibleDate, 0)

// 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
IF (
MaxVisibleDate = _EOMMaxVisibleMonth || MaxVisibleDate=_LastFullMonthEnd,
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)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI