cancel
Showing results for
Did you mean:
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).

Any thoughts to achieve this.

5 REPLIES 5
Community Support Team

## Re: Dynamic Rolling Sum - 4 Quarters Trailing

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.
Regular Visitor

## Re: Dynamic Rolling Sum - 4 Quarters Trailing

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)

Regular Visitor

## Re: Dynamic Rolling Sum - 4 Quarters Trailing

Hi @v-chuncz-msft

Any update on this?

Regards,

Sai

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 )

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.

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.