topic Re: 3 m rolling in DAX Commands and Tips

3 m rolling

jimpatel — Tue, 30 Apr 2024 10:01:55 GMT

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

Re: 3 m rolling

aduguid — Tue, 30 Apr 2024 11:49:54 GMT

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

Re: 3 m rolling

jimpatel — Tue, 30 Apr 2024 11:23:16 GMT

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 😞

Re: 3 m rolling

aduguid — Tue, 30 Apr 2024 11:50:39 GMT

I've updated my answer. Hope this helps.

Re: 3 m rolling

jimpatel — Tue, 30 Apr 2024 12:03:37 GMT

Much appreciated but i am getting below

May i know what i am doing wrong pleasE?

Thanks a lot

Re: 3 m rolling

jimpatel — Tue, 30 Apr 2024 13:03:23 GMT

Any idea of where i am going wrong please?

Thanks a lot again

Re: 3 m rolling

Anonymous — Wed, 01 May 2024 05:44:27 GMT

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

Re: 3 m rolling

jimpatel — Wed, 01 May 2024 09:27:05 GMT

Perfect

Thanks a lot for your kind help 🙂

Re: 3 m rolling

jimpatel — Tue, 21 May 2024 14:14:13 GMT

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

Re: 3 m rolling

jimpatel — Mon, 03 Jun 2024 17:21:08 GMT

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)