Forum Discussion
Calculating moving average
Hi Philip,
Glad it works for you.
regarding your question 2, just remove 31 days to sdate : this is sdate that contains the start date of the first result.regarding 1, there is a function EDATE that should do it:
Amount MAVG2b =
var sdate=Min(Amounts[Date])+365-31 // This is the month when to start displaying results (used below in IF)
var d = 'Amounts'[Date] // the current date
var d365 =EDATE(d,-12) // this calculates a date 12 months before (i assume it works with leap years)
var r=
IF(d>=sdate,
SUMX(
FILTER('Amounts', Amounts[Date] <= d && Amounts[Date] > d365),
[Amount]
)
/12
)
RETURN
r
Hi, For me, this works:
Assuming a table with columns Date and Amount, the calculated column below does the job :
Amount MAVG =
var sdate=Min(Amounts[Date])+365
var d = 'Amounts'[Date]
var d365 =d-365
var r=
IF(d>=sdate;
SUMX( FILTER('Amounts'; Amounts[Date] <= d && Amounts[Date] > d365);
[Amount] )
/12
)
RETURN
r
Hi jmdh,
Thank you for the help. The solution does work, but I am left with two questions you might be able to answer:
1. Does this account for leap years?
2. With this solution, you count the average over all of 2014 (for example) and post it in januari 2015. I would like to have it post to december of 2014. How would you go about that in this solution?
Kind regards,
Philip
Hi Philip,
Glad it works for you.
regarding your question 2, just remove 31 days to sdate : this is sdate that contains the start date of the first result.regarding 1, there is a function EDATE that should do it:
Amount MAVG2b =
var sdate=Min(Amounts[Date])+365-31 // This is the month when to start displaying results (used below in IF)
var d = 'Amounts'[Date] // the current date
var d365 =EDATE(d,-12) // this calculates a date 12 months before (i assume it works with leap years)
var r=
IF(d>=sdate,
SUMX(
FILTER('Amounts', Amounts[Date] <= d && Amounts[Date] > d365),
[Amount]
)
/12
)
RETURN
rWorks perfectly, thanks alot!
Made one small adjustment: used "averagex" in stead of "sumx && /12"
