DAX Formulas for Rolling 12 Month Ending Sums

[AlphaJava]

I am trying to calculate Rolling 12 months ending sums using DAX Formulas to calculate 12 months ending PMPMs (per member per month costs) and PMPM Trends by month. The exhibit below is my data sample. The PowerPivot Table is displayed in BOLD. PMPM and PMPM Trends are fields I want to include in the pivot table using DAX formulas. The excel formula for PMPM 12 months ending Dec-91 is the Sum of Payment from Jan-91 to Dec-91 divided by Sum of Enrollment for the same period for a result of $139. This formula is copied down to Dec-93 (12 months ending PMPM of $163). The PMPM Trends is simply a Year over Year change in PMPMs. For example, the PMPM Trends 12 months ending Dec-92 of 7.2% is calculated as the 12 months ending Dec-92 PMPM of $149 divided by the 12 months ending PMPM a year ago (Dec-91) of $139 minus 1. So the question is how calculate PMPM and PMPM Trends using DAX formulas.

Any help would be greatly appreciated.

Thanks.


[TABLE="width: 520"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]PowerPivot Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD="align: right"]Enrollment[/TD]
[TD="align: right"]Payment[/TD]
[TD="align: right"]PMPM[/TD]
[TD="align: right"]PMPM Trends[/TD]
[/TR]
[TR]
[TD]Jan-91[/TD]
[TD="align: right"]507,000[/TD]
[TD="align: right"]$66,000,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb-91[/TD]
[TD="align: right"]499,000[/TD]
[TD="align: right"]$65,400,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar-91[/TD]
[TD="align: right"]499,000[/TD]
[TD="align: right"]$77,800,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr-91[/TD]
[TD="align: right"]501,000[/TD]
[TD="align: right"]$66,900,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May-91[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]$70,100,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun-91[/TD]
[TD="align: right"]502,000[/TD]
[TD="align: right"]$71,700,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul-91[/TD]
[TD="align: right"]505,000[/TD]
[TD="align: right"]$64,600,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug-91[/TD]
[TD="align: right"]508,000[/TD]
[TD="align: right"]$72,800,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-91[/TD]
[TD="align: right"]501,000[/TD]
[TD="align: right"]$69,300,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct-91[/TD]
[TD="align: right"]499,000[/TD]
[TD="align: right"]$71,600,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-91[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]$71,800,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec-91[/TD]
[TD="align: right"]499,000[/TD]
[TD="align: right"]$68,500,000[/TD]
[TD="align: right"]$139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan-92[/TD]
[TD="align: right"]475,000[/TD]
[TD="align: right"]$70,000,000[/TD]
[TD="align: right"]$140[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb-92[/TD]
[TD="align: right"]472,000[/TD]
[TD="align: right"]$69,900,000[/TD]
[TD="align: right"]$142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar-92[/TD]
[TD="align: right"]473,000[/TD]
[TD="align: right"]$75,200,000[/TD]
[TD="align: right"]$142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr-92[/TD]
[TD="align: right"]471,000[/TD]
[TD="align: right"]$67,500,000[/TD]
[TD="align: right"]$143[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May-92[/TD]
[TD="align: right"]471,000[/TD]
[TD="align: right"]$74,000,000[/TD]
[TD="align: right"]$144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun-92[/TD]
[TD="align: right"]473,000[/TD]
[TD="align: right"]$68,200,000[/TD]
[TD="align: right"]$144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul-92[/TD]
[TD="align: right"]470,000[/TD]
[TD="align: right"]$67,900,000[/TD]
[TD="align: right"]$146[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug-92[/TD]
[TD="align: right"]476,000[/TD]
[TD="align: right"]$74,100,000[/TD]
[TD="align: right"]$147[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-92[/TD]
[TD="align: right"]466,000[/TD]
[TD="align: right"]$64,500,000[/TD]
[TD="align: right"]$147[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct-92[/TD]
[TD="align: right"]467,000[/TD]
[TD="align: right"]$74,800,000[/TD]
[TD="align: right"]$148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-92[/TD]
[TD="align: right"]468,000[/TD]
[TD="align: right"]$71,600,000[/TD]
[TD="align: right"]$149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec-92[/TD]
[TD="align: right"]469,000[/TD]
[TD="align: right"]$64,300,000[/TD]
[TD="align: right"]$149[/TD]
[TD="align: right"]7.2%[/TD]
[/TR]
[TR]
[TD]Jan-93[/TD]
[TD="align: right"]465,000[/TD]
[TD="align: right"]$81,900,000[/TD]
[TD="align: right"]$151[/TD]
[TD="align: right"]7.8%[/TD]
[/TR]
[TR]
[TD]Feb-93[/TD]
[TD="align: right"]456,000[/TD]
[TD="align: right"]$71,900,000[/TD]
[TD="align: right"]$152[/TD]
[TD="align: right"]7.3%[/TD]
[/TR]
[TR]
[TD]Mar-93[/TD]
[TD="align: right"]461,000[/TD]
[TD="align: right"]$75,300,000[/TD]
[TD="align: right"]$153[/TD]
[TD="align: right"]7.4%[/TD]
[/TR]
[TR]
[TD]Apr-93[/TD]
[TD="align: right"]468,000[/TD]
[TD="align: right"]$79,800,000[/TD]
[TD="align: right"]$155[/TD]
[TD="align: right"]8.4%[/TD]
[/TR]
[TR]
[TD]May-93[/TD]
[TD="align: right"]471,000[/TD]
[TD="align: right"]$80,200,000[/TD]
[TD="align: right"]$156[/TD]
[TD="align: right"]8.2%[/TD]
[/TR]
[TR]
[TD]Jun-93[/TD]
[TD="align: right"]473,000[/TD]
[TD="align: right"]$72,500,000[/TD]
[TD="align: right"]$157[/TD]
[TD="align: right"]8.6%[/TD]
[/TR]
[TR]
[TD]Jul-93[/TD]
[TD="align: right"]473,000[/TD]
[TD="align: right"]$77,200,000[/TD]
[TD="align: right"]$158[/TD]
[TD="align: right"]8.6%[/TD]
[/TR]
[TR]
[TD]Aug-93[/TD]
[TD="align: right"]479,000[/TD]
[TD="align: right"]$77,700,000[/TD]
[TD="align: right"]$159[/TD]
[TD="align: right"]8.2%[/TD]
[/TR]
[TR]
[TD]Sep-93[/TD]
[TD="align: right"]473,000[/TD]
[TD="align: right"]$73,900,000[/TD]
[TD="align: right"]$160[/TD]
[TD="align: right"]9.2%[/TD]
[/TR]
[TR]
[TD]Oct-93[/TD]
[TD="align: right"]473,000[/TD]
[TD="align: right"]$86,000,000[/TD]
[TD="align: right"]$162[/TD]
[TD="align: right"]9.4%[/TD]
[/TR]
[TR]
[TD]Nov-93[/TD]
[TD="align: right"]470,000[/TD]
[TD="align: right"]$73,000,000[/TD]
[TD="align: right"]$162[/TD]
[TD="align: right"]8.9%[/TD]
[/TR]
[TR]
[TD]Dec-93[/TD]
[TD="align: right"]469,000[/TD]
[TD="align: right"]$69,700,000[/TD]
[TD="align: right"]$163[/TD]
[TD="align: right"]9.5%[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaJava]

Thanks very much Matt.

 

[AlphaJava]

For the 12 Mo Rolling Enrollment, I want to return blank 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?

=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] )
)
) ))