Rolling sum without filter all function

[Anonymous]

Hi all,

Having some problems with my rolling sum function below, I have slicers on the page so when I use the Filter(ALL( function it removes the interaction with the sliders. Is there anyway to calculate rolling sums without FILTER(ALL)?

Annual_Numeratorr_Rolling =

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)]))

[Anonymous · Accepted solution]

@Anonymous 

You can change all() to ALLSELECTED()

Before not selected:

After the slicer is selected:

the ALL function ignores all filters, regardless of where they are coming from. In contrast, the ALLSELECTED function only ignores filters that are coming from the inner query.

This is the difference between all() and ALLSELECTED():

https://community.powerbi.com/t5/Community-Blog/What-s-the-Main-Difference-Between-ALL-amp-ALLSELECTED-DAX/ba-p/1339726

https://mitchellpearson.com/2020/09/14/understanding-row-context-in-dax-and-power-bi/

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , have you tried a measure like

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

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

tiill last month

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