Rolling data for 4 quarters

[Catherine84]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

In the filter section (right hand side pane), click on Year and select Do not Summarize.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Catherine84]

Thanks 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 · Accepted solution]

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

[Catherine84]

Thanks 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!

[Ashish_Mathur]

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

Hi Ashish,
I have uploaded.

https://1drv.ms/u/s!AmdPdgYKa-3nb77sUTmdz_6TG_I

Hope u can access!

[Ashish_Mathur]

Hi,

The dates in Data Table are wrong.  They all belong to January.  Please recheck.

[Catherine84]

Hi 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!

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

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.

[SANJUJUNNU12_]

hi ashish i need your help on cash flow report

[Ashish_Mathur]

Hi.

What help?

[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 · Accepted solution]

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?

[Catherine84]

Thanks 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 · Accepted solution]

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.