Dynamic Rolling Sum - 4 Quarters Trailing

Question

Hi,&nbsp;I have a requirement to compute rolling sum for the trailing quarters with a dynamic range from Current Quarter till the Last 4 Quarters.&nbsp;For an example, if the Current Fiscal Quarter is '2018-Q2' then the Revenue value should be the sum of "2017-Q3 to 2018-Q2"(as shown in the screenshot).&nbsp;&nbsp;Any thoughts to achieve this.

v-chuncz-msft · Answer

Anonymous,
&nbsp;
You may refer to the following post.&nbsp;
https://community.powerbi.com/t5/Desktop/4-weeks-sales-in-column/m-p/319863#M142375

anonymous · Answer

Hi&nbsp;v-chuncz-msftThe calculation in the above link is not satisfying my requirement. When I use this&nbsp;calculation it is giving me only Q4 of all the years and the numbers are wrong.&nbsp;My requirement is to get last 3 quarters + Current quarter for every quarter.(Example: If we are in 2018-Q2, it should give me {2018-Q2 + 2018-Q1 + 2017-Q4 + 2017-Q3} )&nbsp;Trailing Quarters Sum=VAR S = Table[Segment]VAR Q = Table[QuarterNumber]RETURN&nbsp;&nbsp;&nbsp; IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Q &gt;= 4,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table[Segment] = S&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; Table[QuarterNumber]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Q - 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; Table[QuarterNumber] &lt;= Q&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table[Total_Revenue]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )* QuarterNumber = ROUNDUP(MONTH(Table[Date])/3,0)

anonymous · Answer

Hi&nbsp;v-chuncz-msftAny update on this?Regards,Sai

peter_jeyaraj_i · Answer

Hi,&nbsp;&nbsp;Did you received any update on this topic?&nbsp;i am also come across with the same requirements, then i created one mapping to accomplish ( Actual quarter , Start qtr, End qtr )&nbsp;&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] &lt;= CALCULATE(MIN(PPM_Goal[End_Qtr]))))&nbsp;Now, 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 tables then this last4 quarter logic not working..&nbsp;Help me to fix this issus.&nbsp;

anonymous · Answer

Hi&nbsp;Peter_Jeyaraj_II haven't received any update on this and I haven't implemented this in Power BI.I would suggest you to open a different post for this. Hope they'll reslove it for you.

Forum Discussion

Dynamic Rolling Sum - 4 Quarters Trailing

5 Replies

Related content

Remove trailing commas

Dynamic Current Quarter Filter

Fabric Free Trail

Microsoft Fabric trail

Dynamic Slicer Quarter, Month