Fix the DAY Syntax Error in Excel Power Pivot DAX Measures
Question details
The user needs to resolve a DAY syntax error when editing or copying a DAX measure that uses AVERAGEX and DATESINPERIOD.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- Create or edit a rolling-average sales measure in the Excel Data Model for use in a PivotTable.
- Observed behavior
- The measure may calculate in a PivotTable, but the formula editor reports a syntax error near DAY when the expression is edited or copied.
Open the measure in the Power Pivot measure editor, not a worksheet cell, and confirm that 'Calendar'[Date] and [Total Sales] exist in the Data Model. Check whether your editor expects commas or semicolons as argument separators.
Check the DATESINPERIOD argument separators and DAY keyword
Use this first when the editor highlights DAY or reports an error after copying the measure.
The standard date-column syntax is DATESINPERIOD(date_column, start_date, number_of_intervals, interval). DAY is an interval keyword, not a quoted text value.
The source discussion reports that adding an extra comma before DAY worked in one environment. However, -90,,DAY is not standard four-argument DATESINPERIOD syntax and should not be treated as a general fix.
In Excel, select Power Pivot > Measures > Manage Measures, select the affected measure, and click Edit. Menu names can vary by Excel version.
In the formula field, enter: =AVERAGEX(DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-90,DAY),[Total Sales]). Keep DAY unquoted and use one separator between -90 and DAY.
If your editor uses semicolons, enter: =AVERAGEX(DATESINPERIOD('Calendar'[Date];MAX('Calendar'[Date]);-90;DAY);[Total Sales]). Retype the separators manually to eliminate copy-and-paste punctuation issues.
Click Check Formula if available, then save the measure. In the PivotTable, select a known date range and compare the result with the expected average for that period.
Use DATESBETWEEN to define the rolling date range explicitly
Use this alternative to avoid the DAY interval argument and specify the start and end dates directly.
Use WPS Spreadsheets for Everyday Sales Analysis
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Excel formats, making it easy to move ordinary worksheets and sales reports. It does not fix Excel Power Pivot DAX errors or replace the Power Pivot measure editor; keep DAX-based analysis in a compatible Microsoft tool.
Frequently Asked Questions
Should I add a second comma before DAY?
Not as a general fix. Standard date-column DATESINPERIOD syntax uses four arguments, with one separator between the interval count and DAY. In Power Pivot > Measures > Manage Measures, first test the standard expression and the separator convention required by your editor.
Does DAY need quotation marks in a DAX measure?
No. In DATESINPERIOD, DAY is an interval keyword. Enter DAY without quotation marks in the measure formula; do not enter "DAY" as a text value.
Why does my PivotTable work while the formula editor reports an error?
The PivotTable may be using the last successfully saved measure, while the edited or pasted expression contains different separators or punctuation. Open the measure editor, validate the current expression, save it, and then check the PivotTable again.
Will subtracting 180 in DATESBETWEEN produce a 180-day window?
Not with a complete daily calendar and inclusive endpoints. DATESBETWEEN('Calendar'[Date],MAX('Calendar'[Date])-180,MAX('Calendar'[Date])) covers 181 calendar dates. Subtract 179 for an inclusive 180-day window.
Can WPS Spreadsheets repair this Power Pivot measure?
No. WPS Spreadsheets is suitable for ordinary Excel-format worksheets and exported sales reports, but it is not a Power Pivot DAX measure editor. Correct the measure in Excel Power Pivot; if needed, export the results to a normal worksheet for use in WPS.
