3 m rolling

[jimpatel]

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

[v-yangliu-msft · Accepted solution]

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]

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 

[jimpatel]

Perfect

Thanks a lot for your kind help 🙂

[jimpatel]

Any idea of where i am going wrong please?

Thanks a lot again

[jimpatel]

Much appreciated but i am getting below

May i know what i am doing wrong pleasE?

Thanks a lot

[aduguid]

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

[jimpatel]

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]

I've updated my answer. Hope this helps.