Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago
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's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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.

    Having-trouble-plotting-a-moving-average

     

    Best Regards,
    Dale