Forum Discussion
Help with moving average of a DAX measure
365 Day Moving Average =
Var NumDays = 365
Var LastCurrentDate = MAX(ServiceDetail[Service Date])
Var Period = DATESINPERIOD (ServiceDetail[Service Date]), LastCurrentDate, -NumDays, DAY)
Var Result =
CALCULATE(AVERAGEX(ServiceDetail, [Distinct Transaction]), Period)
VAR FirstDateInPeriod = MINX(Period, ServiceDetail[Service Date])
Var LastDateInPeriod = MAX(ServiceDetail[Service Date])
RETURN
IF (FirstDateInPeriod <= LastDateInPeriod, Result)
As a minimum, to use Time Intelligence function in DAX, like DATESINPERIOD(), you need to have table marked as Date Table. You may connect one from corporate server if you have such table on it (most preferrable way), or create by Power Query, or create Calendar table directly in Power Pivot (less preferable due to maintenance).
In any case be sure it's marked as Date Table.
Link ServiceDetail table with calendar on Date, hide [Service Date] field and use only Calendar dates in measures.
Perhaps something else, but that's better to discuss with sample file.
Thanks Sergei. I do have [Service Date] column marked as a date field.
I figured that I mistakenly copied the AVERAGEX measure from the example...in my source data, each row represents a record of service interaction, so actually I need to count the rows. I've now set up the moving average with a simpler DAX formula as below.
But I'm wondering if there is an alternative measure to DATESINPERIOD that would apply to the 30 preceding dates with observations instead of the 30 preceding calendar days?
Mov Avg =
VAR NumDays = 30VAR RollingCount = CALCULATE(COUNT(‘ServiceDetail’[Activity ID]), DATESINPERIOD(‘ServiceDetail’[Service Date], LASTDATE(‘ServiceDetail’[Service Date]), -NumDays, DAY))RETURNRollingCount/NumDaysMany thanks again.If days are not sequential (e.g. you have only dates with observations) the pattern could be
periodStartDate := VAR backDays = 30 VAR cDate = TODAY () RETURN CALCULATE ( TOPN ( 1, TOPN ( backDays, VALUES ( Table1[Date] ), Table1[Date], DESC ), Table1[Date], ASC ), Table1[Date] <= cDate )