3 month rolling average with Last Value

[Tazmastablasta]

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.

[dax]

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]

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

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Tazmastablasta]

Thank you so much, I will try.

[dax]

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.

[Tazmastablasta]

Hi Zoe Zhi 

Thank you so much for this, but no. My original formula and yours are doing the same thing but The idea is to calulate rolling 3 month avg every month form last submitted score per ID. 

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

And what I am looking for is to Sum only the last submitted value per ID. and calculate 3 month rolling avg.

Do you think you could help me with this ? 

[dax]

Hi

I understand your logic, but if so, I think June(avg April May June) 4+4+*3+5/5= 3.25(16/5), the ID 6  should be 5, right? I will inform you as soon as I get it.

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]

Yes excaly, sorry for the typo. And thankyou so much for your time !