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

6           3      5/19      

3           4      5/19

4           4      6/19

1           3      6/19

6           5      6/19

 

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

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

 

I have search this forum and managed to get to this point(based on this post https://community.powerbi.com/t5/Desktop/Rolling-3-Month-Average/m-p/695325#M335440) , what I need is the way to filter or pass only the lastest values to be used for sum. 

Rolling 3msc = 

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

RETURN
CALCULATE(SUM('Table'[Score]),DATESBETWEEN ( 'Table'[Date], PeriodStart, PeriodEnd)) # what I need to figure out how to sum the score based on the last score in this period 
/
CALCULATE(DISTINCTCOUNT('Table'[ID]),DATESBETWEEN ( 'Table'[Date], PeriodStart, PeriodEnd))

I would appreciate any help.

Need Help

8 Replies

      • dax's avatar
        dax
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Tazmzstablasta,

        Did this help you solve your issue? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

        Thanks for your understanding and support.
        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.

  • 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