Forum Discussion

jimpatel's avatar
jimpatel
Post Patron
2 years ago
Solved

3 m rolling

Hi,   Thanks for looking at my post.   I got stuck with 3 months rolling formula and any help will be much appreciated.   I have data and i wanted to sum the rolling 3 months please.  In this ...
  • Anonymous's avatar
    Anonymous
    2 years ago

     

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

     

    Hi  jimpatel ,

    Here are the steps you can follow：

    1. Create measure.

    Value =
IF(    MAX('Table'[Date])=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Date]),
    SUMX('Table',[Total]),0)
    3 m rolling =
var _enddate=EOMONTH(MAX('Table'[Date]),0)
var _startdate=EOMONTH(MAX('Table'[Date]),-3)
RETURN
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]>=_startdate&&'Table'[Date]<=_enddate),
[Value])

    2. Result:

     

     

    Best Regards,

    Liu Yang

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

aduguid's avatar
aduguid
Memorable Member
2 years ago

 

RollingSum3Months = 
VAR CurrentDate = MAX('YourDateColumn')
RETURN
    CALCULATE(
        SUM('YourValueColumn'),
        DATESINPERIOD (
           Calendar[Date],
           LASTDATE ( PREVIOUSMONTH ( Calendar[Date] ) ),
           -3, MONTH
    )
    )

 

jimpatel's avatar
jimpatel
Post Patron
2 years ago

Thanks for your reply.

 

But i am still getting wrong answer. Am i doing anything wrong here pleasE?

 

I have used above formula in both column and measure and still no luck 😞

 

 

 

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    I've updated my answer. Hope this helps.