# Rolling totals



## paivers (Jan 21, 2013)

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?


----------



## Jacob Barnett (Jan 21, 2013)

Paivers,

You are on the right lines with your thinking! The technique you need to 'reach out' is ALL() wrapped in a CALCULATE() with FILTER() to filter your calendar table (which I am presuming you have). Here is the measure I use regularly for this purpose - no promising it will work for you without having seen your model.

<max('calendar'[date]))
<max('01 -="" calendar'[date]))
 CALCULATE([Sales],
            ALL('01 - Calendar'),
                FILTER(ALL('01 - Calendar'),'01 - Calendar'[Date]>=min('01 - Calendar'[Date])-365&&'01 - Calendar'[Date]<=max('01 - Calendar'[Date]))
                    )
     )


DATESBETWEEN may also be a contender but doesn't have the wider applications of something using ALL() which, IMO, is crucial to understanding DAX.

Suggest you look at Rob's blog for numerous examples of this kind of time intelligence stuff (his book is $10 in colour PDF format from the Mr Excel shop - its worth the price just for the page which shows a formula very similar to this one and why you need both ALL()s).

Jacob

ps apologies the formula looks weird the CODE thing kept cutting off because of '<'</max('01></max('calendar'[date]))


----------



## paivers (Jan 22, 2013)

Thanks Jacob, that seemed to work.  Here's the exact syntax I went with.  At first I was getting 90 days prior + current month for 120 days, so I switched the min to a max, to get about 3 months including the current one.  Its hard to tell if it works exactly as these are 30 day periods rather than months, I could do date arthimatic to calculate months but I prefer regular buckets.  Does it look Ok to you.  Good advice on the book and blog, I'll certainly look into both.  Thanks - Pete

Sum of ED Visits 90 Days:=CALCULATE(sum([ED Visits]),
 ALL('DimDate'),
 FILTER(ALL('DimDate'),'DimDate'[Date]>=MAX('DimDate'[Date])-90&&'DimDate'[Date]<=max('DimDate'[Date])
 )
 )


----------



## Jacob Barnett (Jan 22, 2013)

Pete, your formula looks good. You are right to have changed the MAX to a MIN (my bad).

What it does is use the Pivot table context for the time 'bucket' you've selected and although you are looking at a 'bucket' the whole thing obviously still works around individual dates. This mean that it will effectively use the highest date in your bucket as an the anchor and it will effectively return the 90 days prior to that anchor.

e.g if your 90 days is 2-Oct-2012 to 31-Dec-2012 then the filter is effectively looking for greater than equal to 31-Dec-2012 minus 90 and less than or equal to 31-Dec-2012. 

I am almost 100% than this is returning what you think it is but the only way to check to get hold of the day by day data and check it manually - I pretty much do this with every measure I write in one way or another.

Jacob


----------



## paivers (Jan 22, 2013)

Good, glad I got that right.  I did a validation of sorts by using Excel to count monthly totals, and added up 3 month segments.   I listed out in Excel the last day of each month, eg. 12/31, 11/30 etc. and subracted 90 from each.  Some of the results started on the first, eg. 8/1 vs 8/2, and those were the ones where a monthly sum matched my 90 day sum exactly.  Others were a little short, which is what I expected.  Also I could drill into the cells and the results made sense.  It really was an eye opener for our finance guys who are just so used to counting quarterly number.  In this case we are measuring emergency department visits, and scientific precision means a day difference is important.  DAX is very cool. Thanks again.
   - Pete


----------



## ilovasen (Aug 20, 2014)

Hi

This works well with a Calculated Column as SUM needs a column to calculate. How to do this with a Measure? I get an error when I try to summarize a Measure between two dates.

Brgds,
Inge


----------



## paivers (Aug 20, 2014)

Are you using a date filter as I did in the example.  This actually is in a measure, so long as your column references are in a filter you should be OK.

- Pete


----------



## ilovasen (Aug 20, 2014)

Hi

I am using a date filter. I have a separate DateTable (day-granularity). The Calculated Column is a running total on a Profit & Loss Account. I have made a measure that calculates the monthly values. I then want a rolling 12 month total on that measure. However I get the error message that my measure is not a column and I cannot use the SUM function in the new Rolling 12 month measure.

Calculating the running total on the Calculated column (the running total column) works fine with this formula:

=CALCULATE(SUM([USDProRataAC]), ALL(DateTable[DateKey]), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]>min(DateTable[DateKey]) -365&& DateTable[DateKey]<=max(DateTable[DateKey])))

When I use the Measure:

=CALCULATE(SUM([ACPR ThisMonth]), ALL(DateTable[DateKey]), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]>min(DateTable[DateKey]) -365&& DateTable[DateKey]<=max(DateTable[DateKey])))

then I get the error message "Calculation error in measure 'factFin'[Sum of USDProRataAC Annually]: The SUM function only accepts a column reference as the argument number 1."


- Inge


----------



## scottsen (Aug 20, 2014)

You need to include the table name on this:  SUM ( YourTable[ACPR ThisMonth] )

Minor, but the first ALL(DateTable[DateKey]) is not required.


----------



## ilovasen (Aug 21, 2014)

Thanks scottsen, but this is the issue: I am following Rob's advice on using Measure-references without using the Table-name with which it is defined - for Columns or Calculated Columns I use the Table Name with the Variable name. The [ACPR ThisMonth] is a measure I have had to make in order to transform the data which is stored aggregated within each year (P&L values zeroed at start of year) into a monthly amount.

Is there a work around?

- Inge


----------



## paivers (Jan 21, 2013)

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?


----------



## ilovasen (Aug 21, 2014)

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.


MonthStartDateLastYearMonthStartDateSum of USDProRataACSum of USDProRataAC AnnuallyACPR ThisMonthAAMonths12M01.01.201101.01.20120-14,14801201.02.201101.02.2012-376-14,156-3761201.03.201101.03.2012-563-14,170-1871201.04.201101.04.2012-751-14,189-1881201.05.201101.05.2012-938-14,212-1871201.06.201101.06.2012-1,126-14,241-1881201.07.201101.07.2012-1,309-14,269-1831201.08.201101.08.2012-1,491-14,297-1831201.09.201101.09.2012-1,673-14,325-1821201.10.201101.10.2012-1,858-14,354-1851201.11.201101.11.2012-2,040-14,383-1831201.12.201101.12.2012-2,222-14,347-1811201.01.201201.01.20130-14,34701201.02.201201.02.2013-363-14,335-3631201.03.201201.03.2013-545-14,317-1821201.04.201201.04.2013-727-14,293-1821201.05.201201.05.2013-913-14,268-1861201.06.201201.06.2013-1,096-14,238-1831201.07.201201.07.2013-1,280-14,209-1841201.08.201201.08.2013-1,463-14,180-1841201.09.201201.09.2013-1,647-14,154-1841201.10.201201.10.2013-1,834-14,130-1871201.11.201201.11.2013-2,024-14,114-1901201.12.201201.12.2013-2,212-14,105-18812

<tbody>

</tbody>
TIA for any suggestions.

- Inge


----------



## scottsen (Aug 21, 2014)

> The [ACPR ThisMonth] is a measure



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.


----------



## ilovasen (Aug 24, 2014)

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


----------

