Forum Discussion
Helper IRolling data for 4 quarters
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.
Helper IThanks for your help Ashish. As my dates are not in calendar format, ie Q1: Jun-Aug, Q2: Sep-Nov, Q3: Dec-Feb and Q4: Mar-May.
I manage to build the calendar from the sample that you have shown basis the dates that i need to correspond to the above but when i pull out the rolling data using the formula, its doesnt work. Not sure if this only work on calendar basis?
Rolling 4 quarter value = CALCULATE([Sales QTD Amt],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))
Thanks!
Super UserYou are welcome. The formula should work ireespective of the months that fall in the year. I will need to see yoru file with your formula. I will make changes there.
- Catherine84
- Ashish_Mathur
Hi,
The dates in Data Table are wrong. They all belong to January. Please recheck.
- Catherine84Hi Ashish
https://1drv.ms/u/s!AmdPdgYKa-3ncBrkMbDOVZa6Ia0
Updated the the date. But the rolling still not showing correctly. When I take quarter from data tab and from calendar , seems to show different data as well!
Thanks!