Rolling 3 Month Average

[kristydanda]

Could someone help me how to get Rolling 3 Month Average for Unit Leased? (see picture)

 for example the first Agent B Unit Leased for April = 5, March = 10, Feb = 4. Rolling 3 Month Average for April = 6.33.

The underlining data is something like this

DateCreated    id    agent           leased

02/28/2019      1     B                  4

02/28/2019      2     B                  4

02/28/2019      3     B                  4

02/28/2019      4     B                  4 

etc... repeated until id = 215

03/31/2019      1     B                  10

03/31/2019      2     B                  10 

etc... repeated until id = 179

04/30/2019      1     B                  5

04/30/2019      2     B                  5

etc... repeated until id = 275

from the table below.

Customer Generated = Count ID column

Units Leased = set to Leased with Don't summarize.

If you need file let me know. kc_umass2002@yahoo.com

[v-xicai · Accepted solution]

Hi  @kristydanda ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[v-xicai]

Hi @kristydanda ,

You can try create measures like DAX below.

Period End = LASTDATE('Table1'[DateCreated])

Period Start= FIRSTDATE( DATESINPERIOD('Table1'[DateCreated], [Period End], -3, MONTH))

Rolling 3 Month Average = CALCULATE(AVERAGE('Table1'[leased]),DATESBETWEEN ( 'Table1'[DateCreated], [Period Start], [Period End] ))

Best Regards,

Amy

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

[Tazmastablasta]

Hi v-xicai

I am trying to do something smillar. But what I need is to calcaulate 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.5 (21(Apr,May,Jun)/6) and the value for ID 1=3

This is what I have so far based on your solution 

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.