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
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
Icon for Community Support rankCommunity 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.