如何从PowerPivot数据模型中提取对应月末（或季度末）最后记录日的价格至Excel单个单元格

Got it, let's work through this problem. You've got a massive PowerPivot model (8M+ rows) and need to pull the last recorded Price for a given month or quarter end date—think of it like VLOOKUP's approximate match, where it finds the closest earlier date that exists in your data. Your current CUBE formulas only hit exact dates, so here are two solid ways to fix this:

Method 1: Create a DAX Measure (Recommended for Large Datasets) #

DAX measures are optimized for big data in PowerPivot, so this is the most efficient approach. Follow these steps:

1. Open your PowerPivot window, navigate to the table containing your Date and Price columns.
2. Create a new measure with this DAX code:

Last Price for Target Period = 
VAR TargetEndDate = MAX('YourTableName'[ReferenceDate]) // Use your input date cell value here
VAR PeriodStart = STARTOFMONTH(TargetEndDate) // Swap with STARTOFQUARTER for quarter-end needs
VAR LastExistingDate = CALCULATE(MAX('YourTableName'[Date]), DATESBETWEEN('YourTableName'[Date], PeriodStart, TargetEndDate))
RETURN CALCULATE(MAX('YourTableName'[Price]), 'YourTableName'[Date] = LastExistingDate)

Breakdown: #

• TargetEndDate: Grabs the end date you're targeting (e.g., 30/06/2019 from cell A1).
• PeriodStart: Defines the start of the month (or quarter) for your target date.
• LastExistingDate: Finds the latest date in your data that falls within the target month/quarter.
• Finally, it pulls the Price value tied to that last existing date.

To use this measure in Excel:
Link it to your input date cell (e.g., A1) with this CUBEVALUE formula:

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Last Price for Target Period]", "[YourTableName].[Date].&["&TEXT(A1,"YYYY-MM-DDTHH:MM:SS")&"]")

Method 2: Direct CUBE Formula (No Measure Needed) #

If you prefer not to create a measure, you can embed DAX logic directly into your CUBE functions. Assuming your target date is in cell A1:

=CUBEVALUE("ThisWorkbookDataModel",
    CUBESET("ThisWorkbookDataModel",
        "TOPCOUNT(FILTER([YourTableName].[Date].[Date], [YourTableName].[Date].[Date] <= DATE("&YEAR(A1)&","&MONTH(A1)&","&DAY(A1)&")), 1)",
        "Last Valid Date"
    ),
    "[Measures].[Max of Price]"
)

How this works: #

• FILTER narrows down all dates in your model that are on or before your target date.
• TOPCOUNT(..., 1) grabs the single latest date from that filtered list.
• [Measures].[Max of Price] pulls the Price for that date (since there's only one date, Max returns the exact value).

Key Notes: #

• Ensure your Date column in PowerPivot is formatted as a Date/Time type (not text)—this guarantees date comparisons work correctly.
• For quarter-end dates, adjust the period logic: swap STARTOFMONTH with STARTOFQUARTER in the measure, or modify the FILTER range in the CUBE formula to cover the entire quarter.
• With 8M+ rows, the DAX measure method will perform better long-term, as PowerPivot caches calculations and optimizes queries.

Testing with your sample data:

• Input date 30/06/2019 → returns 58 (the last recorded price in June)
• Input date 31/07/2019 → returns 60 (the last recorded price in July)

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