Rolling totals

[paivers]

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]

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]

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]

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]

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]

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]

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]

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]

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

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

 

[ilovasen]

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