Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago

Summing only one end result

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

 

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

  • jimpatel's avatar
    jimpatel
    Icon for Post Patron rankPost Patron
    2 years ago

    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 🙂