Forum Discussion
Helper IRolling data for 4 quarters
Hi,
Hoping the experts can help me with the dax below.
I have a set of data with quarter but no dates. I need to get a rolling 4 quarters ( 12 months) data for Sales and COGS.
Ie for Q1 2017 Sales = Q4 2016 QTD amt (10)+Q3 2016 QTD amt(20)+Q2 2016 QTD amt(30)+Q1 2017 QTD amt(20) =80
Q2 2017 Sales = Q3 2016 QTD amt(20)+Q4 2016 QTD amt(10)+Q1 2017 QTD amt(20)+Q2 2017 QTD amt(30) = 80
Q3 2017 Sales = Q4 2016 QTD amt(10)+Q1 2017 QTD amt(20)+Q2 2017 QTD amt(30)+Q3 2017 QTD amt(50) = 110
Q4 2017 Sales = Q1 2017 QTD amt(20)+Q2 2017 QTD amt(30)+Q3 2017 QTD amt(50)+Q4 2017 QTD amt(10) = 110
I have many years data as well.
I am trying to write a dax measure that can yield the result above for sales and COGS, can u all please help me!
| Co | Account code | Year | Quarter | QTD amount |
| Co Q | Sales | 2017 | 1 | 20 |
| Co Q | Sales | 2017 | 2 | 30 |
| Co Q | Sales | 2017 | 3 | 50 |
| Co Q | Sales | 2017 | 4 | 10 |
| Co Q | Sales | 2016 | 1 | 40 |
| Co Q | Sales | 2016 | 2 | 30 |
| Co Q | Sales | 2016 | 3 | 20 |
| Co Q | Sales | 2016 | 4 | 10 |
| Co Q | COGS | 2017 | 1 | 5 |
| Co Q | COGS | 2017 | 2 | 2 |
| Co Q | COGS | 2017 | 3 | 4 |
| Co Q | COGS | 2017 | 4 | 6 |
| Co Q | COGS | 2016 | 1 | 3 |
| Co Q | COGS | 2016 | 2 | 5 |
| Co Q | COGS | 2016 | 3 | 7 |
| Co Q | COGS | 2016 | 4 | 8 |
| Co R | Sales | 2017 | 1 | 18 |
| Co R | Sales | 2017 | 2 | 28 |
| Co R | Sales | 2017 | 3 | 48 |
| Co R | Sales | 2017 | 4 | 8 |
| Co R | Sales | 2016 | 1 | 38 |
| Co R | Sales | 2016 | 2 | 28 |
| Co R | Sales | 2016 | 3 | 18 |
| Co R | Sales | 2016 | 4 | 8 |
| Co R | COGS | 2017 | 1 | 3 |
| Co R | COGS | 2017 | 2 | 0 |
| Co R | COGS | 2017 | 3 | 2 |
| Co R | COGS | 2017 | 4 | 4 |
| Co R | COGS | 2016 | 1 | 1 |
| Co R | COGS | 2016 | 2 | 3 |
| Co R | COGS | 2016 | 3 | 5 |
| Co R | COGS | 2016 | 4 | 6 |
Hi,
Remove Account code from the column labels. Try this measure
Rolling 4 quarter sales value = CALCULATE([Sales QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))
Rolling 4 quarter COGS value = CALCULATE([COGS QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))
Hi,
In the filter section (right hand side pane), click on Year and select Do not Summarize.
Hi,
There is no mistake in my formula. I think there is a problem in the Date column of your Data Table. The 4th quarter of 2016 should be March - May of 2017, so the date should be 1 March 2017 (not 1 March 2016 - as is appearing in your PBI file). Please check.
That is all i can help with.
19 Replies
- Ashish_Mathur
Super User
Catherine84Thanks Ashish! :)
For this formula, we used a column data named "Data[QTD amount]". If my calculated data column is a measure instead, how should i modify the formula? Ie like i didnt have a sales/ COGS column but instead i have created a measure call Sales QTD amount using the QTD amount filtered with a category. I cant seems to find the measure to replace the Data[QTD amount] below in the formula.
Rolling 4 quarter value = CALCULATE(SUM(Data[QTD amount]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))
- Ashish_Mathur
Hi,
Remove Account code from the column labels. Try this measure
Rolling 4 quarter sales value = CALCULATE([Sales QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))
Rolling 4 quarter COGS value = CALCULATE([COGS QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))