Forum Discussion
Having trouble plotting a moving average
I have a table where each row represents one case (a technical support case).
The table has the following form.
I have created a figure that shows the number of cases per week for many weeks.
To do this, I created a measure CaseCount
CaseCount = COUNTA(MY_TABLE[CASENUMBER])
and plotted it against a measure WeekNumber
WeekNumber = WEEKNUM(TABLE[CREATEDDATE].[Date],2)
This works as expected.
I'd like to simultaneously plot a smoothed version of this curve, such as a moving average. So I created another measure that is intended to represent a 2-week moving average.
MovAvg = CALCULATE(
COUNTA( MY_TABLE[CASENUMBER] ),
DATESINPERIOD( MY_TABLE[CREATEDDATE].[Date], MAX(MY_TABLE[CREATEDDATE]), -14, DAY )
) / 2
The problem is that I can't seem to get the moving average to work. This is the result that I currently have when I add my moving average curve to the figure.
I'm having trouble understanding why the result is incorrect. Does anyone know what may have gone wrong, or whether there is a better way to do something like this?
Thanks.
Hi Anonymous,
It's always a good idea to create a Date table for the Time Intelligence functions. Because the fact table could have discontinuous dates, the MAX(MY_TABLE[CREATEDDATE]) may not the end of a week.
1. Create a Date table.
Calendar = ADDCOLUMNS(CALENDARAUTO(), "WeekNum", WEEKNUM([Date], 2)
2. Establish a relationship.
3. The measure could be like below.
MovAvg = CALCULATE ( COUNTA ( MY_TABLE[CASENUMBER] ), DATESINPERIOD ( calendar[DATE], MAX ( calendar[DATE] ), -14, DAY ) ) / 2
Best Regards,
Dale
3 Replies
Hi Anonymous,
It's always a good idea to create a Date table for the Time Intelligence functions. Because the fact table could have discontinuous dates, the MAX(MY_TABLE[CREATEDDATE]) may not the end of a week.
1. Create a Date table.
Calendar = ADDCOLUMNS(CALENDARAUTO(), "WeekNum", WEEKNUM([Date], 2)
2. Establish a relationship.
3. The measure could be like below.
MovAvg = CALCULATE ( COUNTA ( MY_TABLE[CASENUMBER] ), DATESINPERIOD ( calendar[DATE], MAX ( calendar[DATE] ), -14, DAY ) ) / 2
Best Regards,
Dale- Thanks very much Dale. One last question if you don't mind: When I attempt to use Calendar = ADDCOLUMNS(CALENDARAUTO(), "MyCalendar", WEEKNUM(MY_TABLE[Date],2)) I see the error "The expression refers to multiple columns. Multiple columns cannot be converted into a scalar value." Do you know what may have caused this?
Hi Anonymous,
That's because it is a table formula. You need to create a new table with it. Please refer to the snapshot below.
Best Regards,
Dale