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 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
Community 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?
  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    This worked well for me, you can refer to the image above or see the attachment above.

    Your formula looks right to me. Not sure what went wrong for you.