Forum Discussion

Tazmastablasta's avatar
Tazmastablasta
Icon for Helper I rankHelper I
7 years ago

3 month rolling average with Last Value

Hi all   This is my problem.  I am trying to calculate 3 month avarge every month (rolling) with last value.   For exmaple    ID   SCORE DATE  2           3      3/19 1           2      4/19 ...
Need Help
dax's avatar
dax
Icon for Community Support rankCommunity Support
7 years ago

Hi Tazmastablasta,

I change your expression for rolling average

Rolling 3msc = 

VAr PeriodEnd = LASTDATE('avg'[Date])
VAR PeriodStart = FIRSTDATE( DATESINPERIOD('avg'[Date], PeriodEnd, -3, MONTH))

RETURN
CALCULATE(SUM('avg'[Score]),DATESBETWEEN ( 'avg'[Date], PeriodStart, PeriodEnd)) /
CALCULATE(COUNT('avg'[ID]),DATESBETWEEN ( 'avg'[Date], PeriodStart, PeriodEnd))

It will sum score based on current context in  visual, you will see the result is different when I add id in table

But I don't understand the logic of "the value for ID 1=2", if possible, could you please explain this in details？

Best Regards,
Zoe Zhi

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

  • Tazmastablasta's avatar
    Tazmastablasta
    Icon for Helper I rankHelper I
    7 years ago

    Hi Zoe Zhi

    Thank you so much for this, The idea is to calulate rolling 3 month avg every month form last submitted score. 

     

    * just noticed a mistake in my original post, do apologise

     

    So May average would be  would be 3 (12(Mar,Apr,May)/4) and the value for ID 1 Score would be =2 

    But in June average would be would be 3.8 (19(Apr,May,Jun)/5) and the value for ID1 score would be =3

     

    Like so 

     

    ID   SCORE DATE 

    2           3      3/19

    1          * 2      4/19

    6           3      5/19            May(avg Mar,Apr,May)  3+*2+3+4/4 =   3

    3           4      5/19

    4           4      6/19            June(avg April May June) 3+4+4+*3+5/5= 3.8

    1          * 3      6/19

    6           5      6/19