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. ...
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