Getting previous 3MMA

[Easley06]

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.

[v-chenwuz-msft · Accepted solution]

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.

[tamerj1]

@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
    )
)

[Easley06]

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

[tamerj1]

@Easley06 
Sorry, can you please clarify further?

[Easley06]

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 

[tamerj1]

@Easley06 

Any chance you can share sample file?