Forum Discussion

RanjanThammaiah's avatar
RanjanThammaiah
Helper V
6 years ago
Solved

3 months Moving Average

 

 

Hi All,

 

Can some one help me to calculate the 3 months Moving average for the below table?

 

FiscalPeriodOfCloseTotal Value
16081867.9
25585706.8
32727995.2
47655452
52193328.2
65853917.4
72913116.1
81560797.6
9186928.5

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to create a check column first:

     

    Check = ('Table'[FiscalPeriodOfClose]-1)/3-ROUNDDOWN('Table'[FiscalPeriodOfClose]/3,0)

     

    Then create a group column:

     

    Group = ROUNDUP('Table'[FiscalPeriodOfClose]/3,0)

     

    Create a measure:

     

    3Months Average = 
IF (
    MAX ( 'Table'[Check] ) = 0,
    SUM ( 'Table'[Total Value] ),
    CALCULATE (
        SUM ( 'Table'[Total Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Group] IN DISTINCT ( 'Table'[Group] )
                && 'Table'[FiscalPeriodOfClose] <= MAX ( 'Table'[FiscalPeriodOfClose] )
        )
    )
)/3

     

    The result shows:

    Here is my test pbix:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    RanjanThammaiah 

    Try Like

    CALCULATE(Average(table[Total Value]),filter(All(Table),table[FiscalPeriodOfClose]>=max(Table[FiscalPeriodOfClose])-3 &&
									table[FiscalPeriodOfClose] <= max(table[FiscalPeriodOfClose])))

     

    The ideal would with date and date calendar 

    Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))

     

    if you need more help make me @

    Appreciate your Kudos.

     

     

    • RanjanThammaiah's avatar
      RanjanThammaiah
      Helper V
      6 years ago

      Hey,

       

      Thanks, I have used the second formula. But the numers calculation seems to be wrong. I have added how it should be in excel with Formula below.

       

       

      I need the numbers should be like this.

      FiscalPeriodOfCloseTotal Value3Months AverageFormula Used
      16081867.9                 2,027,289=SUM(B2)/3
      25585706.8                 3,889,192=SUM(B2:B3)/3
      32727995.2                 4,798,523=SUM(B2:B4)/3
      47655452                 2,551,817=SUM(B5)/3
      52193328.2                 3,282,927=SUM(B5:B6)/3
      65853917.4                 5,234,233=SUM(B5:B7)/3
      72913116.1                    971,039=SUM(B8)/3
      81560797.6                 1,491,305=SUM(B8:B9)/3
      9186928.5                 1,553,614=SUM(B8:B10)/3

       

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try to create a check column first:

     

    Check = ('Table'[FiscalPeriodOfClose]-1)/3-ROUNDDOWN('Table'[FiscalPeriodOfClose]/3,0)

     

    Then create a group column:

     

    Group = ROUNDUP('Table'[FiscalPeriodOfClose]/3,0)

     

    Create a measure:

     

    3Months Average = 
IF (
    MAX ( 'Table'[Check] ) = 0,
    SUM ( 'Table'[Total Value] ),
    CALCULATE (
        SUM ( 'Table'[Total Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Group] IN DISTINCT ( 'Table'[Group] )
                && 'Table'[FiscalPeriodOfClose] <= MAX ( 'Table'[FiscalPeriodOfClose] )
        )
    )
)/3

     

    The result shows:

    Here is my test pbix:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi