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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , example measure with help from date table

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD("Date"[Date ],MAX("Date"[Date ]),-12,MONTH))

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.