Remove Future Dates from Rolling 12 Month Measures

CatManKuhn 

My measure absolutely works the way you want. It's an adaptation of the technique from www.sqlbi.com by Alberto and Marco. It uses a technique known as "the interception of filters" to do the right thing. I have used it in many other projects and it's always worked correctly. I would really be surprised if it didn't do what it's supposed to. It surely does.

And, of course, it's not true what you say: "I dot not have that issue with the 27 until I use your measure." If you take a look at your very first post in this thread... you'll notice that, indeed, you also have a blank row paired with the numer 26,438. It means the blank row either does exist in your data (it might be a blank or an empty text ""), or the model is creating it due to RI problems. There is NO other possibility.

By the way, I'm talking about this measure (just to be sure we're talking about the same thing):

Rolling 12 Month External Hiring Sum =
var VeryLastDateWithAnyHires =
    CALCULATE(
        MAX( 'Date'[Date] ),
        'Date'[DatesWithHires],
        ALL( 'Date' )
    )
var TotalPeriodWithHires =
    CALCULATETABLE(
        DISTINCT( 'Date'[Date] ),
        'Date'[Date] <= VeryLastDateWithAnyHires
    )
var EffectiveDates =
    INTERSECT(
        TotalPeriodWithHires,
        DISTINCT( 'Date'[Date] )
    )
var MaxEffectiveDate =
    MAXX( EffectiveDates, 'Date'[Date] )
var Result =
    CALCULATE(
        COUNTROWS( 'External Hires' ),
        DATESINPERIOD(
            'Date'[Date],
            MaxEffectiveDate,
            -1,
            YEAR
        ),
        KEEPFILTERS( 'Date'[DatesWithHires] )
    )
return
    Result