Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 '2...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

Hi v-chuncz-msft

Any update on this?

Regards,

Sai

  • Peter_Jeyaraj_I's avatar
    Peter_Jeyaraj_I
    Helper II
    7 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.