Past 4 Quarter Rolling Data Issues while applying Quarter filter

Question

Hi,&nbsp;&nbsp;i have task to compute the past 4 quarters sum dynamically based on the quarter field in base tablethen i created one mapping to accomplish ( Actual quarter , Start qtr, End qtr )Mapping TableActul Quarter&nbsp; &nbsp; StartQtr&nbsp; &nbsp; EndQtr20171&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20161&nbsp; &nbsp; &nbsp; &nbsp;20164&nbsp;then applied below DAX to get running 4 qtr total.&nbsp;Last4Qtr_Production = CALCULATE([Measures_Count],FILTER(,Table[Quarter_Field] &gt;= CALCULATE(MIN(PPM_Goal[Start_Qtr])) &amp;&amp;Table[Quarter_Field]&nbsp;&lt;= CALCULATE(MIN(PPM_Goal[End_Qtr]))))Sum of Past 4 quarters works fine&nbsp;but&nbsp;i am facing other issues.. that if i apply quarter filter from mapping then other metric calculation not workingIn case if i apply quarter filter from main table then this last4 quarter logic not working..&nbsp;Help me to fix this issus.&nbsp;

v-danhe-msft · Answer

Hi&nbsp;Peter_Jeyaraj_I,
Could you please offer me some sample data and post your desired result if possible?
&nbsp;
Regards,
Daniel He

v-danhe-msft · Answer

Hi&nbsp;Peter_Jeyaraj_I,
Could you please offer me some sample data and post your desired result if possible?
&nbsp;
Regards,
Daniel He

peter_jeyaraj_i · Answer

Thanks for your responseFor ex.. i have the Quarter wise count &amp; i want to derive the last 4 quarter sum.. ie., 20151 quarter we need to consider 20141 to 20144 . here i below i shared some sample data.&nbsp;Also, i tried to achive w/o mapping/calendar table as it's breaks other dax expression that i build in main table.&nbsp;&nbsp;QuarterCountLast4Q Count2014112020142231220143163520144565120151161072015227111201532011520154601192016120123201623112720163241312016464135

anonymous · Answer

Hi Peter_Jeyaraj_I&nbsp;Try the following&nbsp;1. After loading data go to Edit Query.&nbsp;2. Sort the Table by th Quarter Column&nbsp;3. Add Index column from 1&nbsp;4. Create&nbsp;the measure&nbsp;&nbsp;&nbsp; RunningTotal = CALCULATE(&nbsp;SUM( Table1[Count] ),&nbsp;FILTER(&nbsp; ALL(Table1) ,&nbsp;SUMX( FILTER( Table1, EARLIER( Table1[Index] ) = Table1[Index] -4 ||&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EARLIER( Table1[Index] ) = Table1[Index] -3 ||&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EARLIER( Table1[Index] ) = Table1[Index] -2 ||&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EARLIER( Table1[Index] ) = Table1[Index] - 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) , Table1[Count] )&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;If this works for you please accept this as solution and also give KUDOS.&nbsp;Cheers&nbsp;CheenuSing&nbsp;Here is the sample output.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

peter_jeyaraj_i · Answer

Sure.. thanks for your response. let me try and keep you updated on the status.&nbsp;

Forum Discussion

Past 4 Quarter Rolling Data Issues while applying Quarter filter

7 Replies

Related content

Rolling accumulation

Rolling accumulation

Rolling quarters

Rolling data for 4 quarters

Rolling 4 quarter sum for each selected quarter

Quarter	Count	Last4Q Count
20141	12	0
20142	23	12
20143	16	35
20144	56	51
20151	16	107
20152	27	111
20153	20	115
20154	60	119
20161	20	123
20162	31	127
20163	24	131
20164	64	135