Need help with Rolling Sum

[Anonymous]

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

)))

[v-angzheng-msft · Accepted solution]

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]

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]

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

[Anonymous]

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]

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]

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]

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]

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]

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.

[Anonymous]

Thanks Zeon your the best!!!

[Anonymous]

Thanks Zeon! it worked!

[Ashish_Mathur]

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.

[Anonymous]

Hi Anish,

Please see if you can access the PBI file through this link, still not able to figure out rolling annual sums yet. Please not I have removed alot of the sensitive data so it only shows the raw numbers. Would appreciate any help I could get.

https://app.powerbi.com/links/TPIfR9zS15?ctid=c05a8755-7450-4345-8568-2fd90358e31b&pbi_source=linkSh...

[Ashish_Mathur]

That just takes me to a sign-in page.

[amitchandak]

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

[Anonymous]

Hi Amit,

Please let me know if you can access this link, not sure if you can access it. What I am trying to do is to divide the 12 month rolling sum of the total numerator by the 12 month rolling sum of the total denominator.

https://app.powerbi.com/links/TPIfR9zS15?ctid=c05a8755-7450-4345-8568-2fd90358e31b&pbi_source=linkSh...