Forum Discussion

Easley06's avatar
Easley06
Helper II
4 years ago
Solved

Getting previous 3MMA

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.
Moving average
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Easley06
      Helper II
      4 years ago

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

       

       

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.