Summing only one end result

[jimpatel]

Hi,

Thanks for looking at my post.

Any help will be appreciated 

I have below table and i would like to get only one number as sum please. Currently it is summing each and every row and giving massive number. What i am expecting please is for Feb 2024 expecting result as 9 please and so on

3M = CALCULATE(SUM('Table1'[Colum3]),DATESINPERIOD('Calender'[Dates],MAX('Calender'[Dates]),-3,MONTH))

Thanks a lot

[jimpatel]

Thanks for your reply. 

I think i did not explain properly.

What i am after is 3 months of rolling average. 

In this example above for Feb 2024 = Dec 2023 + Jan 2024+Feb 2024 = 0 + 6 + 3 = 9

For Mar 2024 = JAn 2024 + Feb 2024 + Mar 2024 = 6 +3 + 7 = 16

Hope this make sense.

Really appreciate your help 🙂

[Anonymous]

Thanks for the reply from @lbendlin , please allow me to provide another insight:

Hi @jimpatel ,

How was outcome 9 derived? One can try to modify the formula as follows:

3M =
VAR MaxDate =
    MAX ( 'Calender'[Dates] )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Colum3] ),
        FILTER ( ALL ( 'Calender' ), 'Calender'[Dates] = MaxDate )
    )

Best Regards,
Adamk Kong

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

[lbendlin]

You can't use the MAX inside the CALCULATE like this.  Move the MAX out into a variable.