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]

 

[Matt Allington]

Well first you need a calendar table with an ID column. Read about ID columns here Power Pivot Calendar Tables -

create a 12 month rolling enrolment total like this

=calculate(sum(tablename[enrolment]),filter(all(calendar),calendar[id]>=min(calendar[id])-12 && calendar[id] <= max(calendar[id])))

use the the same principle to create a total payments number. Then divide them out. Finally you can work out the %

 

[AlphaJava]

Matt:

Thank you very much for your response. Your suggestion works very well. Great Stuff!! Really appreciate it. Many thanks to you again.

 

[AlphaJava]

Matt:

False alarm; after a closer look, the formula did not work after all. If returns the same enrollment and payment amount by month not the sum of 12 months of enrollment or payments. Thanks anyways for your help; the formula is definitely a good start to getting the correct results that I am expecting.

 

[Matt Allington]

I'm. It really sure what the problem is that you describe. This formula will take the current row in your pivot table and then go back in time 12 months and add up all the enrolment numbers to get a total. Actually the formula should be -11 and not -12. So in Dec 91, that would be approximately 6 million

 

[AlphaJava]

Thanks Matt. Yes I was expecting 6.02M for Dec-91, 5.99M for Jan-92, 5.96M for Feb-92, ...., 5.63M for Dec-93. I believe the issue is that the powerpivot table above is already a summarize table for enrollment and payments. So when I try to insert a calculated field for Rolling 12 Mos of enrollment using the formula, it requires using the detail table for enrollment not the summarized one displayed above. As a result, the formula gives the same amounts by month as noted in the summarized powerpivot table. I was hoping that the formula would have used only the information provided in the summarized pivot table and not the underlying data of the pivot table. In regular pivot tables one can insert calculated fields using only the information in the pivot table and not the underlying data. It seems that is not as straightforward for powerpivot tables. Thank you very much for you time; I really appreciate it.

 

[Matt Allington]

Well I am sure I can help you, but I need to see the workbook, or a sample of it

 

[scottsen]

Best guess: If you made a Calendar table as Matt correctly recommends, make sure you drop fields from the Calendar table onto the pivot table (not the Dates from your fact/data table). His formula looks good to me...

 

[AlphaJava]

Matt:

I send you a sample of the workbook via email. The data is a little different because I created the sample from scratch. However the formulas for the results remain the same. Thanks in advance.

 

[Matt Allington]

Thanks for sharing the file. Here is the link for others. https://dl.dropboxusercontent.com/u/30711565/PowerPivot_Question.xlsx
There are a few issues.
1. Firstly your tables are not connected the correct way. I have added a sheet with before and after.
2. Secondly the dates in your calendar table don't line up with the dates in your data tables. The calendar table has to cover the range of your data.
3. The third issue is that your months in your pivot table is coming from the wrong table. You have to select months from the calendar table for your pivot.

When you fix all three - it works. You may want to put some IF statements around your rolling total so that it returns blank for the first 11 months of data (where there is not a valid 12 month total).

Regards

Matt