Pivot Table Issues

How to Show Monthly Columns and Year-to-Date Totals in Excel Power Pivot

Olivia Miller Oct 9, 2026 869 views

Question details

The user needs to create a profit and loss report that displays 12 fiscal-month columns alongside a year-to-date (YTD) total column, including future months that currently have no data.

How to Show Monthly Columns and Year-to-Date Totals in Excel Power Pivot

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a comprehensive fiscal year profit and loss report using Power Pivot and time-intelligence measures.
Observed behavior: The goal is to structure a PivotTable to properly sort fiscal months, calculate YTD totals using DAX, and display consistent monthly columns even for future blank periods.

Before you start

Ensure the Power Pivot add-in is enabled in your Excel application and that your raw dataset contains a valid date column to relate to a dedicated Calendar/Date table.

Solution 1Recommended

Configure a Date Table and Use DAX Time-Intelligence Measures

This method relies on building a robust Power Pivot Data Model to calculate Year-to-Date totals accurately while forcing the PivotTable to display all fiscal months chronologically.

To properly utilize DAX time-intelligence functions like TOTALYTD, you must use a contiguous Date table rather than relying on the dates found in your raw data (fact table).

Additionally, ensuring future blank months appear requires adjusting the built-in PivotTable field settings.

Create and Link a Date Table

Add a dedicated Date table to your Power Pivot Data Model containing contiguous dates. In the Power Pivot Diagram View, draw a relationship connecting the Date column in your fact table to the Date column in this new Date table.

Sort Months Chronologically

To prevent month names from sorting alphabetically (e.g., April before January), select your Date table in the Data View. Click the 'Month Name' column, go to the Home tab, click 'Sort by Column', and choose your 'Fiscal Month Number' column.

Create the YTD Measure

In the Power Pivot calculation area below your fact table, formulate your Year-to-Date measure using the TOTALYTD function. Type: YTD Amount := TOTALYTD([Total Amount], Dates[Date]) and press Enter.

Show Blank Future Months

Insert a PivotTable from the Data Model. Drag your sorted 'Month Name' field to the Columns area. Right-click any month header in the PivotTable, choose 'Field Settings', navigate to the 'Layout & Print' tab, and check 'Show items with no data' to ensure future months remain visible.

Configure a Date Table and Use DAX Time-Intelligence Measures

Data Consistency: Checking 'Show items with no data' ensures your report layout remains perfectly consistent month-over-month, even as you slice by different years or categories that lack data.

WPS Spreadsheet Solution

Create Monthly and YTD Pivot Tables Easily in WPS Office

You do not always need complex Power Pivot models or DAX formulas to track your profit and loss. WPS Spreadsheet allows you to create standard PivotTables, group dates into months, and easily add a Year-to-Date (YTD) running total using intuitive, built-in features.

1. Insert a PivotTable: Select your dataset in WPS Spreadsheet, navigate to the Insert tab, and click PivotTable to create a new report.
2. Group by Month: Drag your Date field to the Columns area. Right-click any date in the column headers, select 'Group', and choose 'Months' to consolidate your data.
3. Add Values and Running Totals: Drag your Amount field to the Values area twice. Right-click the second Amount field, select 'Show Values As', and choose 'Running Total In' based on your Date field to automatically display your YTD figures.

Fully compatible with Microsoft Excel (.xlsx) file formats and PivotTablesCreate Monthly columns effortlessly using automated Date GroupingGenerate Year-to-Date totals quickly with the built-in 'Running Total In' featureLightweight, fast, and features a familiar user interface for immediate productivity

microsoft office alternative - wps office

Frequently Asked Questions

How do I show future months with no data in my PivotTable?

Right-click the specific Date or Month field header in your PivotTable, select 'Field Settings', navigate to the 'Layout & Print' tab, and check the box for 'Show items with no data'. This forces the table to render the column even if the underlying total is blank.

Why does my TOTALYTD measure show blank or incorrect values?

This usually happens if your fact table is not properly linked to a continuous Date table. Ensure your Data Model contains a dedicated Calendar table, verify the one-to-many relationship in Diagram View, and confirm you are referencing the Calendar date column inside your TOTALYTD formula.

How can I sort month names by fiscal month instead of alphabetically?

Open the Power Pivot window and select your Date table in Data View. Click on your 'Month Name' column, go to the Home tab, click 'Sort by Column', and choose your 'Fiscal Month Number' column to enforce proper chronological sorting.

Do I need Power Pivot to calculate Year-to-Date totals?

No. In standard Excel or WPS Spreadsheet, you can drag your numeric field into the PivotTable Values area twice, right-click one of the columns, select 'Show Values As', and choose 'Running Total In' to calculate YTD without writing any DAX formulas.

Excel & Data Problems