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 to edit it? A photo of my data is linked below as well.

 

Rolling Average Numerator =
Var MAXDATE = MAX('PowerBI(Index)'[Return_id])
Return
CALCULATE(
SUM('PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)]),
FILTER(
ALL('PowerBI(Index)'[Return_id]),
AND(
'PowerBI(Index)'[Return_id]<=MAXDATE,
DATEADD(
'PowerBI(Index)'[Return_id],
1,
YEAR
) > MAXDATE
)))
 

 

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.

16 Replies

  • 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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Sharing a photo will not help.  You should create a measure to solve your question.  Furthermore, there should be a Calendar Table as well.  to get specific help, share the link from where i can download your PBI file.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    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

      Thanks Zeon! it worked!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey Zeon,

       

      Actually I have one more question, my page has slicers but when the FILTER ALL function is used it overrides the slicer filtering. Is there a way to still calculate rolling summation without the filter all function?

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

        Hi, Anonymous 

         

        just replace ALL with AllSELECTED like this:

        code:

        Rolling Average Numerator =
VAR _sum12 =
    CALCULATE (
        SUM ( 'PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)] ),
        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 )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'PowerBI(Index)'[Return_id] ),
        _sum12,
        SUM ( 'PowerBI(Index)'[Total Return Numerator ("calculated reutrn" tab, Column K)] )
    )

         

         

        Best Regards,
        Community Support Team _ Zeon Zheng