topic Re: Getting previous 3MMA in DAX Commands and Tips

Getting previous 3MMA

Easley06 — Wed, 20 Apr 2022 06:10:03 GMT

Hi,

can someone help me to figure out how to get this?

currently i have 3MMA but how to get Jan 2022 and Nov, Dec 2021

measure for 3mma

3MMA SOM = CALCULATE(AVERAGEX(values(mv_fact_sales_aggr[MonthYear]),[SOM]),DATESINPERIOD(mv_fact_sales_aggr[date],LASTDATE(mv_fact_sales_aggr[date]),-3,MONTH))

Thanks.

Re: Getting previous 3MMA

tamerj1 — Wed, 20 Apr 2022 02:49:36 GMT

Easley06

Doupt it works properly without Date table but you may try

Previous 3MMA SOM = CALCULATE ( AVERAGEX ( VALUES ( mv_fact_sales_aggr[MonthYear] ), [SOM] ), DATESINPERIOD ( mv_fact_sales_aggr[date], DATEADD ( LASTDATE ( mv_fact_sales_aggr[date] ), -3, MONTH ), -3, MONTH ) )

Re: Getting previous 3MMA

Easley06 — Wed, 20 Apr 2022 03:05:38 GMT

it works but i have year for 2022 and i want to get also the Nov , Dec 2021 and Jan 2022 is it possible?

Re: Getting previous 3MMA

tamerj1 — Wed, 20 Apr 2022 03:15:11 GMT

Easley06
Sorry, can you please clarify further?

Re: Getting previous 3MMA

Easley06 — Wed, 20 Apr 2022 06:08:37 GMT

Hi sir,

what i mean is i have 2022 data whice has 4months and i like to get also the previous 3MMA

currently the measure you gave only works if quarter is complete

would like to get the jan 2022 , Dec and nov 2021

Re: Getting previous 3MMA

tamerj1 — Wed, 20 Apr 2022 06:41:49 GMT

Easley06

Any chance you can share sample file?

Re: Getting previous 3MMA

v-chenwuz-msft — Fri, 22 Apr 2022 09:29:31 GMT

Hi Easley06 ,

Please enable the Time Intelligence. This will let the data column has Date Hierarchy.

Create a measure via the expression below.

Measure = CALCULATE ( AVERAGE ( 'Table'[Values] ), FILTER ( 'Table', [Date] >= DATE(YEAR(TODAY()),MONTH(TODAY())-5,1) ) )

This measure will filter the context and if the context have no rows match the condition, it will return blank.

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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