DAX Formulas for Rolling 12 Month Ending Sums – YOY % Change

[AlphaJava]

I am trying to calculate a YOY PMPM % change using the formula below, but I am not getting the expected results. What is wrong with my formula?

(CALCULATE (
CALCULATE (
[PMPM],
FILTER (
ALL ( Calendar ),
Calendar[DateID]
>= MIN ( Calendar[DateID] ) -0
&& Calendar[DateID] <= MAX ( Calendar[DateID] )
)
),
FILTER (
Calendar,
Calendar[Month] < DATE ( 1991, 1, 1 )
|| Calendar[Month] > DATE ( 1992, 11, 1 )
)
) -
(CALCULATE (
CALCULATE (
[PMPM],
FILTER (
ALL ( Calendar ),
Calendar[DateID]
>= MIN ( Calendar[DateID] ) - 11
&& Calendar[DateID] <= MAX ( Calendar[DateID] )
)
),
FILTER (
Calendar,
Calendar[Month] < DATE ( 1991, 1, 1 )
|| Calendar[Month] > DATE ( 1992, 11, 1 )
)
))) / ( CALCULATE (
CALCULATE (
[PMPM],
FILTER (
ALL ( Calendar ),
Calendar[DateID]
>= MIN ( Calendar[DateID] ) - 11
&& Calendar[DateID] <= MAX ( Calendar[DateID] )
)
),
FILTER (
Calendar,
Calendar[Month] < DATE ( 1991, 1, 1 )
|| Calendar[Month] > DATE ( 1992, 11, 1 )
)
)
)

 

[Ozeroth]

I suggest you create an intermediate measure or measures, and check each component is evaluating correctly.
Also DAX Formatter by SQLBI is useful for formatting & making sense of measures with nested functions.

For example the first measure taken from above could be:

=
CALCULATE (
    CALCULATE (
        [PMPM],
        FILTER (
            ALL ( Calendar ),
            Calendar[DateID]
                >= MIN ( Calendar[DateID] ) - 0
                && Calendar[DateID] <= MAX ( Calendar[DateID] )
        )
    ),
    FILTER (
        Calendar,
        Calendar[Month] < DATE ( 1991, 1, 1 )
            || Calendar[Month] > DATE ( 1992, 11, 1 )
    )
)

By the way, I think the FILTER on Calendar in this first portion of the measure is not needed (as it doesn't actually change the context), but it is needed in the subsequent versions.

 

[AlphaJava]

Thanks very much Ozeroth.