Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
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
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
98 | |
97 | |
38 | |
38 |
User | Count |
---|---|
153 | |
122 | |
76 | |
73 | |
66 |