Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
mpg06
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!

CustomerDateQuantityExpected Result
11/1/201510
12/1/201520
13/1/201540
14/1/201520
15/1/201510
16/1/201520
17/1/201510
18/1/201530
19/1/201540
110/1/201510
111/1/201510
112/1/2015123
11/1/2016123
12/1/2016122
13/1/2016119
14/1/2016219
15/1/2016422
16/1/2016222
17/1/2016122
21/1/201520
22/1/201510
23/1/201530
24/1/201540
25/1/201510
26/1/201510
27/1/201510
28/1/201510
29/1/201510
210/1/201510
211/1/201510
212/1/2015118
21/1/2016117
22/1/2016117
23/1/2016115
24/1/2016112
25/1/2016112
26/1/2016112
27/1/2016112
28/1/2016112
3 REPLIES 3
mike_honey
Memorable Member
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.

 

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.

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 trial_powerBI.JPG

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

 

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.