Having trouble plotting a moving average

[Anonymous]

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.

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

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?

[v-jiascu-msft]

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