Dynamic Rolling Sum - 4 Quarters Trailing

[Anonymous]

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.

[v-chuncz-msft]

@Anonymous,

You may refer to the following post. 

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

[Anonymous]

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)

[Anonymous]

Hi @v-chuncz-msft

Any update on this?

Regards,

Sai

[Peter_Jeyaraj_I]

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. 

[Anonymous]

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.