Forum Discussion

RanjanThammaiah's avatar
RanjanThammaiah
Icon for Helper V rankHelper V
6 years ago
Solved

Moving Average nbased on week Nuber from the Table

Hi All,

 

I have a querry on 3 months moving average. I have a data based on the weeks where i need to calculate the 3 months moving average.

i have tried with the below formulas but seems not to be working.

Data in the table:

ID'sValueClose date Week
759984035678.51
809002140.712
8090024974.953
8090024974.954
80900-24974.955
759984014271.45
759984028542.86
75998402140717
7599840-2140718
8090024261.389
759984021407110
18800035678.511
10110104994.9912
10110104994.9913
75808016055.3314
80900-4102.2315
809004102.2316
809004102.2317
1450514271.418

 

Pivoted:

Close date WeekSum of Value3 Months MA
135678.535678.5
22140.712140.71
324974.9524974.95
424974.9535678.5
5-10703.552140.71
628542.824974.95
721407135678.5
8-2140712140.71
924261.3824974.95
1021407135678.5
1135678.52140.71
124994.9924974.95
134994.9935678.5
1416055.332140.71
15-4102.2324974.95
164102.2335678.5
174102.232140.71
1814271.424974.95

 

I have created the below formula. Kindly check and let me know if there are anything wrong in it.

Formula 1:
3 Months MA =
CALCULATE (
SUM(Sheet1[Total Value] ),
DATESINPERIOD (
Sheet1[EffectiveCloseDate],LASTDATE(Sheet1[EffectiveCloseDate]),28,DAY))
/CALCULATE(DISTINCTCOUNT(Sheet1[EffectiveCloseDate]),
DATESINPERIOD(Sheet1[EffectiveCloseDate],LASTDATE(Sheet1[EffectiveCloseDate]),28,DAY))
 
Formula 2:
3 Months MA = SUMX(FILTER(Sheet1,[Close date Week]=Sheet1[Close date Week]-1),Sheet1[Total Value])
 
 

Regards,

Ranjan

 

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

    Hi, RanjanThammaiah 

     

    Based on your description, I assume that your requirement is to calculate the average value of previous three weeks. I created data as follows.

    Table:

     

    You may create a measure as follows.

     

    3 weeks MA = 
var _week = SELECTEDVALUE('Table'[Close date Week])

return 
IF(
    _week>=4,
    CALCULATE(
        AVERAGE('Table'[Value]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Close date Week]>=_week-3&&
            'Table'[Close date Week]<=_week-1
        )
    ),
    0
)

     

     

    Result:

     

    If I misunderstand your thoughts, please show me your expected result. Do mask sensitive data before uploading. Thanks.

     

    Best Regards

    Allan

     

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

     

2 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, RanjanThammaiah 

     

    Based on your description, I assume that your requirement is to calculate the average value of previous three weeks. I created data as follows.

    Table:

     

    You may create a measure as follows.

     

    3 weeks MA = 
var _week = SELECTEDVALUE('Table'[Close date Week])

return 
IF(
    _week>=4,
    CALCULATE(
        AVERAGE('Table'[Value]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Close date Week]>=_week-3&&
            'Table'[Close date Week]<=_week-1
        )
    ),
    0
)

     

     

    Result:

     

    If I misunderstand your thoughts, please show me your expected result. Do mask sensitive data before uploading. Thanks.

     

    Best Regards

    Allan

     

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