Forum Discussion
Rolling 3 Month Average
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. [email protected]
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
3 Replies
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.
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.
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
