cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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
3 REPLIES 3
Memorable Member

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.

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.

Frequent Visitor

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])
)```

Memorable Member

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors