基于发票表创建滚动月度Dynamic Monthly Pivot Table的编号排序问题

Hey Matt, let's work through that dynamic rolling monthly pivot table issue you're stuck on—specifically the month numbering and sorting part. I'll cover two common scenarios: Excel Power Pivot (since you mentioned a dynamic pivot) and SQL, in case you're working with database data.

1. Excel Power Pivot Solution (Dynamic Rolling Months) #

Step 1: Add Calculated Columns for Rolling Month Numbering #

First, we need to assign the correct rolling number to each invoice date, then fix the sorting issue. The key here is separating the numeric sort value from the display label (so 1 shows as "01" but sorts correctly next to "02").

Numeric Sort Column (For Correct Ordering)

Add this calculated column in Power Pivot to get a sortable numeric value:

Rolling Month Num = 
VAR BaseMonth = EOMONTH(TODAY(), 0) // Anchor to last day of current month (avoids partial month issues)
VAR InvoiceMonth = EOMONTH('InvoiceTable'[INVOICEDATE], 0)
VAR MonthOffset = DATEDIFF(InvoiceMonth, BaseMonth, MONTH) + 1 // Current month =1, last month=2, same month last year=13
RETURN
IF(MonthOffset <=13, MonthOffset, BLANK()) // Only keep last 13 months

Display Label Column (For "01" to "13" Format)

Add a second column to format the number as a two-digit label:

Rolling Month Label = FORMAT('InvoiceTable'[Rolling Month Num], "00")

Step 2: Build the Dynamic Pivot Table #

• Drag ITEMCODE to the Rows area
• Drag Rolling Month Label to the Columns area
• To fix sorting: Right-click any column label → Sort → More Sort Options → Select "Sort by column" → Choose Rolling Month Num (this ensures "01" comes before "02" instead of sorting alphabetically)
• Drag UNITS to the Values area, set to Sum

2. SQL Solution (If Working with Database Data) #

If you're generating the pivot directly in SQL (e.g., SQL Server), here's how to handle numbering and sorting:

Step 1: Calculate Rolling Month Values #

First, create a CTE to add the rolling month number and formatted label:

WITH InvoiceRollingMonths AS (
    SELECT
        ITEMCODE,
        UNITS,
        -- Numeric value for sorting (current month =1, same month last year=13)
        DATEDIFF(MONTH, EOMONTH(INVOICEDATE, 0), EOMONTH(GETDATE(), 0)) +1 AS RollingMonthNum,
        -- Formatted label for display ("01" to "13")
        FORMAT(DATEDIFF(MONTH, EOMONTH(INVOICEDATE, 0), EOMONTH(GETDATE(), 0)) +1, '00') AS RollingMonthLabel
    FROM InvoiceTable
    -- Filter to only last 13 months of data
    WHERE INVOICEDATE >= DATEADD(MONTH, -12, EOMONTH(GETDATE(), 0))
)

Step 2: Pivot with Explicit Column Order #

By explicitly listing the columns in order, you guarantee the correct sort:

SELECT
    ITEMCODE,
    ISNULL([01], 0) AS [01],
    ISNULL([02], 0) AS [02],
    ISNULL([03], 0) AS [03],
    ISNULL([04], 0) AS [04],
    ISNULL([05], 0) AS [05],
    ISNULL([06], 0) AS [06],
    ISNULL([07], 0) AS [07],
    ISNULL([08], 0) AS [08],
    ISNULL([09], 0) AS [09],
    ISNULL([10], 0) AS [10],
    ISNULL([11], 0) AS [11],
    ISNULL([12], 0) AS [12],
    ISNULL([13], 0) AS [13]
FROM InvoiceRollingMonths
PIVOT (
    SUM(UNITS)
    FOR RollingMonthLabel IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12], [13])
) AS PivotResult
ORDER BY ITEMCODE;

Quick Troubleshooting Tips #

• If your pivot columns are out of order, always double-check that you're sorting by the numeric Rolling Month Num column, not the text label (alphabetical sorting would put "10" before "02" otherwise)
• To use a fixed base month instead of the current system month (e.g., end of last month), replace TODAY()/GETDATE() with your target date (e.g., DATE(2024,5,31) for May 2024)

内容的提问来源于stack exchange，提问作者MattC