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 instance in below data, 

 

3M rolling for Mar 2024 will be 35 ( 30 + 3 +2)

3M rolling for Feb 2024 will be 30 (3+2+25)

3M rolling for Jan 2024 will be 27 (0+25+2) and so on....

 

Data snapshot:

 

 

3m rolling which is wrong:

 

Formula used for 3m rolling :

 

any idea please?

 

thanks a lot

 

  • 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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • jimpatel's avatar
      jimpatel
      Post Patron
      2 years ago

      Perfect

       

      Thanks a lot for your kind help 🙂

    • jimpatel's avatar
      jimpatel
      Post Patron
      2 years ago

      Sorry for reopening this post.

      May i know how to change below formula please.

       

      * This formula is summing data if it is falling on same day. That is i wanted to take one value or average value if it is falling on same date please.  Any help will be highly appreciated.

       

      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)

       

      The reason i found is , if 2 rows fall in the minimum criteria the code is adding those values. Is there anyway to not to do that pleasE?

       

       

      Thanks a lot 

       

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

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Much appreciated but i am getting below

     

    May i know what i am doing wrong pleasE?

     

    Thanks a lot

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Any idea of where i am going wrong please?

     

    Thanks a lot again

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Sorry for reopening this post.

    May i know how to change below formula please.

     

    * This formula is summing data if it is falling on same day. That is i wanted to take one value or average value if it is falling on same date please.  Any help will be highly appreciated.

     

    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)