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.
Super UserHi,
The dates in Data Table are wrong. They all belong to January. Please recheck.
Helper Ihttps://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!
- Ashish_Mathur
Hi,
In the First visual, i dragged year and quarter from the Calendar Table. Also, in the slicer, drag the Year from the Calendar Table.
- Catherine84
Hi Ashish,
Thanks for your kind reply :)
1) I tried to drag the year from the Calendar, but it doesnt come out as expected. When i tried to change the format to Date (YYY), it becomes all 1905 instead of the 2018.
2) The sum of the Q1,Q2,Q3 and Q4 sales QTD added up to 15,155,801 instead of the row under Rolling 4 quarter value under Q4
17,806,760
- Ashish_Mathur
Hi,
In the filter section (right hand side pane), click on Year and select Do not Summarize.
- Catherine84
Thanks Ashish_Mathur. That works!
But the rolling 4th quarter numbers is still not summing up rightly. :) Expecting the numbers to be 15,155,801 instead of 17,806,760
- Ashish_Mathur
Hi,
I cannot understand your data. What does the Amount column in the Data Table hold. Does that hold the figures for that quarter ot is it data till that specific quarter? Why have you had to compute the QTD Amount column in the Data Table?
- Catherine84Thanks for the reply.
The amount column holds the year to date numbers. Ie for q1 =June - Aug, q2=Aug -Nov,q3=Aug -Feb, q4=mar-may.
The QTd amount holds 3 months amount, ie q1=june -aug, q2 =sep-nov,q3=dec-feb and q4=mar-may.
I need a column that calculate rolling 4 quarters numbers. Ie if 2018 3rd quarter, I will need 2018q1+2018q2+2018q3+2017q4. For 4the quarter of 2018=2018q1+2018q2+2018q3+2018q4. 😀 - Ashish_Mathur
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.
- Catherine84
Thanks Ashish! Thanks for your help!! I manage to get to it ! As my calendar [year] and the Data [Year] is different, it didnt work intitally! Really appreciate all your kind help! Has learned many things from this thread! :)
Thanks a lot !
- Ashish_Mathur
You are welcome. If my reply helped, please mark my relevant post as Answer.
hi ashish i need your help on cash flow report
- Ashish_Mathur
Hi.
What help?
