cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saeruva Regular Visitor
Regular Visitor

Dynamic Rolling Sum - 4 Quarters Trailing

Hi,

 

I have a requirement to compute rolling sum for the trailing quarters with a dynamic range from Current Quarter till the Last 4 Quarters. 
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).

 RollingSum.jpg

 

Any thoughts to achieve this.

5 REPLIES 5
Community Support Team
Community Support Team

Re: Dynamic Rolling Sum - 4 Quarters Trailing

@saeruva,

 

You may refer to the following post. 

https://community.powerbi.com/t5/Desktop/4-weeks-sales-in-column/m-p/319863#M142375

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
saeruva Regular Visitor
Regular Visitor

Re: Dynamic Rolling Sum - 4 Quarters Trailing

Hi @v-chuncz-msft

The calculation in the above link is not satisfying my requirement. When I use this calculation it is giving me only Q4 of all the years and the numbers are wrong.

 

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

 

Trailing Quarters Sum=

VAR S = Table[Segment]

VAR Q = Table[QuarterNumber]

RETURN

    IF (

        Q >= 4,

        SUMX (

            FILTER (

                Table,

                Table[Segment] = S

                    && Table[QuarterNumber]

                    > Q - 4

                    && Table[QuarterNumber] <= Q

            ),

            Table[Total_Revenue]

        )

    )

* QuarterNumber = ROUNDUP(MONTH(Table[Date])/3,0)

DynamicRollSum.jpg

saeruva Regular Visitor
Regular Visitor

Re: Dynamic Rolling Sum - 4 Quarters Trailing

Hi @v-chuncz-msft

Any update on this?

Regards,

Sai

Peter_Jeyaraj_I Regular Visitor
Regular Visitor

Re: Dynamic Rolling Sum - 4 Quarters Trailing

Hi, 

 

Did you received any update on this topic? 

i am also come across with the same requirements, then i created one mapping to accomplish ( Actual quarter , Start qtr, End qtr )

 mapping

 

then applied below DAX to get running 4 qtr total.

 

Last4Qtr_Production = CALCULATE(
[Measures_Count],
FILTER(,
Table[Quarter_Field] >= CALCULATE(MIN(PPM_Goal[Start_Qtr])) &&
Table[Quarter_Field] <= CALCULATE(MIN(PPM_Goal[End_Qtr])))
)

 

Now, i am facing other issues.. that if i apply quarter filter from mapping then other metric calculation not working

In case if i apply quarter filter from main tables then this last4 quarter logic not working.. 

Help me to fix this issus. 

saeruva Regular Visitor
Regular Visitor

Re: Dynamic Rolling Sum - 4 Quarters Trailing

Hi @Peter_Jeyaraj_I

I 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.