Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need help with Rolling Sum

Hi new to PowerBI here,   Having problems with a 12 month rolling sum function, basically it takes current month data + data from the past 11 months. Any idea of another way to calculate it or how ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Anonymous 

     

    Try to create a measure like this:

    Rolling Average Numerator =
VAR _sum12 =
    CALCULATE (
        SUM ( 'PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)] ),
        FILTER (
            ALL ( 'PowerBI(Index)' ),
            EOMONTH ( 'PowerBI(Index)'[Return_id], 0 ) <= EOMONTH ( MAX ( [Return_id] ), 0 )
                && EOMONTH ( 'PowerBI(Index)'[Return_id], 0 )
                    > EOMONTH ( MAX ( [Return_id] ), -12 )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'PowerBI(Index)'[Return_id] ),
        _sum12,
        SUM ( 'PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)] )
    )

    my sample data:

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Zeon,

 

Thank you alot for your help. I have another question, is it possible to exclude the first 12 months for the rolling sum? It is still adding values for the first year when there are no values prior to the first date.

v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

Not clear enough for me to understand what exactly you want to acheive,  to share more details about it or to draw a simple picture to show your expected visual.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Ang,

     

    Please see below an example below of what I mean. So it doesn't make sense to take a rolling 12 month sum when there isn't 12 months of data before the current month. What I'm trying to have is to adjust the equation so that if it cant find a full 12 months of data then do not calculate a value.

     

    Not sure if it is clear enough for you.

     

     

    • v-angzheng-msft's avatar
      v-angzheng-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, Anonymous 

       

      Use the IF function to determine if the number of rows in the table is 12.

      try this:

      _Allselected_filter_first12 =
VAR _table =
    FILTER (
        ALLSELECTED ( 'Table' ),
        EOMONTH ( 'Table'[Date], 0 ) <= EOMONTH ( MAX ( [Date] ), 0 )
            && EOMONTH ( 'Table'[Date], 0 ) > EOMONTH ( MAX ( [Date] ), -12 )
    )
VAR _sum12 =
    IF ( COUNTROWS ( _table ) = 12, CALCULATE ( SUM ( 'Table'[Sale] ), _table ) )
RETURN
    IF ( HASONEVALUE ( 'Table'[Date] ), _sum12, SUM ( 'Table'[Sale] ) )

      result:

       

       

      Best Regards,
      Community Support Team _ Zeon Zheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago
        Annual_Numeratorr_Rolling_Sum2 =
        var _table=
        FILTER (
        ALLSELECTED('PowerBI(Index)'),
        EOMONTH ( 'PowerBI(Index)'[Return_id], 0 )
        <= EOMONTH(MAX([Return_id]),0)&&EOMONTH('PowerBI(Index)'[Return_id],0)>EOMONTH(MAX([Return_id]),-12)
        )
        VAR _sum12 =
        IF(COUNTROWS(_table)=12,CALCULATE(SUM('PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)]),_table))
        RETURN
        IF(HASONEVALUE('PowerBI(Index)'[Return_id]),_sum12,SUM('PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)]))
         
        For some reason, the function only returns one value on the first date, does the above look right to you?