- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rolling 12 Month total by customer
Need to get sum of quantity for previous 12 months by Customer. For instance: For Customer 1 July 2016 total should be from August 2015 - July 2016. This should be grouped by customer. If 12 months prior data does not exist, it can be 0 or blank. Is it possible to calculate a column or measure? Thanks!
Customer | Date | Quantity | Expected Result |
1 | 1/1/2015 | 1 | 0 |
1 | 2/1/2015 | 2 | 0 |
1 | 3/1/2015 | 4 | 0 |
1 | 4/1/2015 | 2 | 0 |
1 | 5/1/2015 | 1 | 0 |
1 | 6/1/2015 | 2 | 0 |
1 | 7/1/2015 | 1 | 0 |
1 | 8/1/2015 | 3 | 0 |
1 | 9/1/2015 | 4 | 0 |
1 | 10/1/2015 | 1 | 0 |
1 | 11/1/2015 | 1 | 0 |
1 | 12/1/2015 | 1 | 23 |
1 | 1/1/2016 | 1 | 23 |
1 | 2/1/2016 | 1 | 22 |
1 | 3/1/2016 | 1 | 19 |
1 | 4/1/2016 | 2 | 19 |
1 | 5/1/2016 | 4 | 22 |
1 | 6/1/2016 | 2 | 22 |
1 | 7/1/2016 | 1 | 22 |
2 | 1/1/2015 | 2 | 0 |
2 | 2/1/2015 | 1 | 0 |
2 | 3/1/2015 | 3 | 0 |
2 | 4/1/2015 | 4 | 0 |
2 | 5/1/2015 | 1 | 0 |
2 | 6/1/2015 | 1 | 0 |
2 | 7/1/2015 | 1 | 0 |
2 | 8/1/2015 | 1 | 0 |
2 | 9/1/2015 | 1 | 0 |
2 | 10/1/2015 | 1 | 0 |
2 | 11/1/2015 | 1 | 0 |
2 | 12/1/2015 | 1 | 18 |
2 | 1/1/2016 | 1 | 17 |
2 | 2/1/2016 | 1 | 17 |
2 | 3/1/2016 | 1 | 15 |
2 | 4/1/2016 | 1 | 12 |
2 | 5/1/2016 | 1 | 12 |
2 | 6/1/2016 | 1 | 12 |
2 | 7/1/2016 | 1 | 12 |
2 | 8/1/2016 | 1 | 12 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is a common requirement that you would expect to be easily solved with a single function or formula. But unfortunately DAX is saddled with several shockingly poor design decisions in this area, that have still not been corrected many years later.
For a glimpse of the full horror, try this article:
http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
Note it breezily assumes you start with a tidy Calendar table which in your case will need to extend 12 months prior to your earliest date.
Personally I would go back to the Edit Queries layer and resolve it there - the tool behaves more rationally and predictably there.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried all of that, din't work. The closest I came to the solution was using code below from one of the forum discussions: It gives me
Cumulative2 = VAR RowDate = Sheet1[Date] VAR RowCust = Sheet1[Customer] RETURN CALCULATE ( SUM ( Sheet1[Quantity] ), FILTER ( Sheet1, MONTH(RowDate) - Month(Sheet1[Date])<=12 && YEAR ( RowDate) - YEAR ( Sheet1[Date] )<=1 && Sheet1[Customer] = RowCust ), VALUES(Sheet1[Customer]) )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your results look really odd, and nothing like the expected results you originally posted. For example your first row shows 35, where you expected 0.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
10-10-2024 11:04 PM | |||
Anonymous
| 06-19-2024 06:16 PM | ||
07-23-2024 01:29 PM | |||
06-04-2024 03:05 AM | |||
07-18-2024 06:01 AM |
User | Count |
---|---|
137 | |
107 | |
85 | |
59 | |
46 |