DAX Formulas for Rolling 12 Month Ending Sums – DATESBETWEEN () and BLANK () Functions

[AlphaJava]

For the “12 Mo Rolling Enrollment”, I want to return blank for dates between dates 1/1/1991 and 11/1/1991 using the DATESBETWEEN () and blank () functions as shown in the formula below, but the formula is returning blanks for all dates. What am I doing wrong? Any help would be greatly appreciated. Thanks.

=IF(CALCULATE(SUM ( Population[Enrollment] ), DATESBETWEEN(Calendar[Month], DATE(1991,1,1), DATE(1991,11,1) )), BLANK(), (CALCULATE (
SUM ( Population[Enrollment] ),
FILTER (
ALL ( Calendar ),
Calendar[DateID]
>= MIN ( Calendar[DateID] ) - 11
&& Calendar[DateID] <= MAX ( Calendar[DateID] )
)
)
)
)

 

[Ozeroth]

I think the easiest way to produce a version of the "12 Month Rolling Enrollment" measure that returns blank for a particular date range is to remove that date range from the filter context, by wrapping the original measure inside another CALCULATE.

I can think of two ways to do it (first might be faster):

1.

=
[COLOR=#ff0000][B]CALCULATE ([/B][/COLOR]
    CALCULATE (
        SUM ( Population[Enrollment] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[DateID]
                >= MIN ( Calendar[DateID] ) - 11
                && Calendar[DateID] <= MAX ( Calendar[DateID] )
        )
    )[B][COLOR=#ff0000],
    FILTER (
        Calendar,
        Calendar[Month] < DATE ( 1991, 1, 1 )
            || Calendar[Month] > DATE ( 1991, 11, 1 )
    )
)[/COLOR][/B]

2.

=
[COLOR=#ff0000][B]CALCULATE ([/B][/COLOR]
    CALCULATE (
        SUM ( Population[Enrollment] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[DateID]
                >= MIN ( Calendar[DateID] ) - 11
                && Calendar[DateID] <= MAX ( Calendar[DateID] )
        )
    )[COLOR=#ff0000][B],[/B][/COLOR]
[COLOR=#ff0000][B]    FILTER ([/B][/COLOR]
[COLOR=#ff0000][B]       Calendar,[/B][/COLOR]
[COLOR=#ff0000][B]        NOT ([/B][/COLOR]
[COLOR=#ff0000][B]            CONTAINS ([/B][/COLOR]
[COLOR=#ff0000][B]                DATESBETWEEN ([/B][/COLOR]
[COLOR=#ff0000][B]                    Calendar[Month],[/B][/COLOR]
[COLOR=#ff0000][B]                    DATE ( 1991, 1, 1 ),[/B][/COLOR]
[COLOR=#ff0000][B]                    DATE ( 1991, 11, 1 )[/B][/COLOR]
[COLOR=#ff0000][B]                ),[/B][/COLOR]
[COLOR=#ff0000][B]                Calendar[DateID], Calendar[DateID][/B][/COLOR]
[COLOR=#ff0000][B]            )[/B][/COLOR]
[COLOR=#ff0000][B]        )[/B][/COLOR]
[COLOR=#ff0000][B]    )[/B][/COLOR]
[COLOR=#ff0000][B])[/B][/COLOR]

 

[AlphaJava]

Ozeroth:

Thank you very much.