Rolling totals

P

paivers

Board Regular
Joined
Sep 4, 2009
Messages
64
I have a measure which sums records based on a filter, which typically is used in a monthly bucket. Now I would like to sum a rolling 12 month total. So if you look at the Jan 2013 measure it would include Dec 12, Nov 12 and so forth. I'm part of the way there in that I calculate the MaxDate in the context (e.g. 1/31/13), and want to SUMX with a filter of RowDate>MaxDate-365. The problem is that the only dates visible in the January 2013 context are Jan 2013 ones. I kind of need to reach out to the full context when doing that SUMX, whereas the MaxDate needs to respect the context. Any ideas how to address this, or simpler ways of doing rolling totals?
 
Sort by date Sort by votes
Hi

Further to my case above: Below please find a table of the data. The Measure I would like to add up 12 months for and roll over monthly is the Measure [ACPR ThisMonth]. The [Sum of USDProRataAC Annually] measure calculates the rolling 12 month total of the [Sum of USDProRataAC] column. However when I try to change the formula from SUM([USDProRataAC]) to SUM([ACPR ThisMonth] I get the following error message: "Calculation error in measure 'factFin'[Sum of USDProRataAC Annually]: The SUM function only accepts a column reference as the argument number 1."

Sum of USDProRataAC Annually:=CALCULATE(SUM([USDProRataAC]), ALL(DateTable[DateKey]), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]>min(DateTable[DateKey]) -365&& DateTable[DateKey]<=max(DateTable[DateKey]))) works fine.

Sum of ACPR ThisMonth:=CALCULATE(SUM([ACPR ThisMonth]), ALL(DateTable[DateKey]), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]>min(DateTable[DateKey]) -365&& DateTable[DateKey]<=max(DateTable[DateKey]))) gives error message.

[TABLE="width: 523"]
<tbody>[TR]
[TD]MonthStartDateLastYear[/TD]
[TD]MonthStartDate[/TD]
[TD]Sum of USDProRataAC[/TD]
[TD]Sum of USDProRataAC Annually[/TD]
[TD]ACPR ThisMonth[/TD]
[TD]AAMonths12M[/TD]
[/TR]
[TR]
[TD="align: right"]01.01.2011[/TD]
[TD="align: right"]01.01.2012[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-14,148[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.02.2011[/TD]
[TD="align: right"]01.02.2012[/TD]
[TD="align: right"]-376[/TD]
[TD="align: right"]-14,156[/TD]
[TD="align: right"]-376[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.03.2011[/TD]
[TD="align: right"]01.03.2012[/TD]
[TD="align: right"]-563[/TD]
[TD="align: right"]-14,170[/TD]
[TD="align: right"]-187[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.04.2011[/TD]
[TD="align: right"]01.04.2012[/TD]
[TD="align: right"]-751[/TD]
[TD="align: right"]-14,189[/TD]
[TD="align: right"]-188[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.05.2011[/TD]
[TD="align: right"]01.05.2012[/TD]
[TD="align: right"]-938[/TD]
[TD="align: right"]-14,212[/TD]
[TD="align: right"]-187[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.06.2011[/TD]
[TD="align: right"]01.06.2012[/TD]
[TD="align: right"]-1,126[/TD]
[TD="align: right"]-14,241[/TD]
[TD="align: right"]-188[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.07.2011[/TD]
[TD="align: right"]01.07.2012[/TD]
[TD="align: right"]-1,309[/TD]
[TD="align: right"]-14,269[/TD]
[TD="align: right"]-183[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.08.2011[/TD]
[TD="align: right"]01.08.2012[/TD]
[TD="align: right"]-1,491[/TD]
[TD="align: right"]-14,297[/TD]
[TD="align: right"]-183[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.09.2011[/TD]
[TD="align: right"]01.09.2012[/TD]
[TD="align: right"]-1,673[/TD]
[TD="align: right"]-14,325[/TD]
[TD="align: right"]-182[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.10.2011[/TD]
[TD="align: right"]01.10.2012[/TD]
[TD="align: right"]-1,858[/TD]
[TD="align: right"]-14,354[/TD]
[TD="align: right"]-185[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.11.2011[/TD]
[TD="align: right"]01.11.2012[/TD]
[TD="align: right"]-2,040[/TD]
[TD="align: right"]-14,383[/TD]
[TD="align: right"]-183[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.12.2011[/TD]
[TD="align: right"]01.12.2012[/TD]
[TD="align: right"]-2,222[/TD]
[TD="align: right"]-14,347[/TD]
[TD="align: right"]-181[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.01.2012[/TD]
[TD="align: right"]01.01.2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-14,347[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.02.2012[/TD]
[TD="align: right"]01.02.2013[/TD]
[TD="align: right"]-363[/TD]
[TD="align: right"]-14,335[/TD]
[TD="align: right"]-363[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.03.2012[/TD]
[TD="align: right"]01.03.2013[/TD]
[TD="align: right"]-545[/TD]
[TD="align: right"]-14,317[/TD]
[TD="align: right"]-182[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.04.2012[/TD]
[TD="align: right"]01.04.2013[/TD]
[TD="align: right"]-727[/TD]
[TD="align: right"]-14,293[/TD]
[TD="align: right"]-182[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.05.2012[/TD]
[TD="align: right"]01.05.2013[/TD]
[TD="align: right"]-913[/TD]
[TD="align: right"]-14,268[/TD]
[TD="align: right"]-186[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.06.2012[/TD]
[TD="align: right"]01.06.2013[/TD]
[TD="align: right"]-1,096[/TD]
[TD="align: right"]-14,238[/TD]
[TD="align: right"]-183[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.07.2012[/TD]
[TD="align: right"]01.07.2013[/TD]
[TD="align: right"]-1,280[/TD]
[TD="align: right"]-14,209[/TD]
[TD="align: right"]-184[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.08.2012[/TD]
[TD="align: right"]01.08.2013[/TD]
[TD="align: right"]-1,463[/TD]
[TD="align: right"]-14,180[/TD]
[TD="align: right"]-184[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.09.2012[/TD]
[TD="align: right"]01.09.2013[/TD]
[TD="align: right"]-1,647[/TD]
[TD="align: right"]-14,154[/TD]
[TD="align: right"]-184[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.10.2012[/TD]
[TD="align: right"]01.10.2013[/TD]
[TD="align: right"]-1,834[/TD]
[TD="align: right"]-14,130[/TD]
[TD="align: right"]-187[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.11.2012[/TD]
[TD="align: right"]01.11.2013[/TD]
[TD="align: right"]-2,024[/TD]
[TD="align: right"]-14,114[/TD]
[TD="align: right"]-190[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01.12.2012[/TD]
[TD="align: right"]01.12.2013[/TD]
[TD="align: right"]-2,212[/TD]
[TD="align: right"]-14,105[/TD]
[TD="align: right"]-188[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

TIA for any suggestions.

- Inge
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
The [ACPR ThisMonth] is a measure
Click to expand...

Oh! Well, you can see how I was confused, because you were converting from a calc column (where table name is optional) and you were calling SUM and you can't call SUM on a measure! :) Good on you for following that standard, it really is super useful.

So... I am only 1 cup of coffee into my day, and honestly didn't read the most recent entry... so, yell at me when this isn't useful, but...

In general, if you want to SUM a measure... you use SUMX instead. But... [ACPR ThisMonth] is gonna freak out if you pass it all 12 months?

Something about your pattern doesn't feel right.
 
Upvote 0
Solved!

I had previously tried your solution of using SUMX but it only gave me a result like "Sum of ProRataAC Annualy". The reason was that I used the factFin table as the table reference in the SUMX formula, because I thought I should use the table with which the [ACPR ThisMonth] measure was defined. However, receiving scottsen' tip above I also tried with the "DateTable" as the table reference in the SUMX formula and WOILA!! the result is what I wanted.

Sum of ACPR ThisMonth:=IF( HASONEVALUE( DateTable[MonthOfYear]), CALCULATE(SUMX(DateTable, [ACPR ThisMonth]), ALL(DateTable[DateKey]), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]>min(DateTable[DateKey]) -365&& DateTable[DateKey]<=max(DateTable[DateKey]))))

Thanks to scottsen (again!) and Palvers for hanging in there with me. Your responses gives me an extra push to try again, and again, and......

brgds,
Inge
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Rolling 12 months total using DAX formula
Replies
0
Views
2K
Kane_Heramb
K
D
  • Question Question
Rolling total for last month as caluclated column
Replies
0
Views
486
dicken
D
D
  • Question Question
DAX measure calculate sum of current and one previous month
Replies
0
Views
797
dicken
D
F
  • Question Question
PowerPivot / DAX measure: Best 3 month period of sales
Replies
3
Views
769
fparadis1970
F
T
  • Question Question
Cumulative Total of Category Occurances by Date
Replies
0
Views
431
TwinJohnson
T

Forum statistics

Threads
1,224,041
Messages
6,176,028
Members
452,697
Latest member
CuriousSpreadsheet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top