求助：DAX中如何用历史通胀指数生成现金流索引计算列

Hey Francesco, let's get that cash flow indexing working properly! Your initial formula was on the right track, but there are a couple of key adjustments needed to handle context and inflation factor calculation correctly.

What Was Wrong With the Original Formula? #

• Inflation Index Handling: You're using the raw index % value directly in PRODUCT, but we need to convert it to a growth factor (e.g., 2% inflation becomes 1.02 instead of 2) to avoid shrinking your cash flow over time.
• Context Filtering: Since Table A and Table B have a one-to-many relationship, the default row context might restrict your FILTER to only the matching year instead of all prior years. We need to clear that default filter to get all relevant inflation data.

Correct DAX Calculation Column Formula #

cashflow_index = 
VAR CurrentYear = A[year]
// Get all inflation factors (converted to growth multipliers) for years up to current row's year
VAR InflationGrowthFactors = 
    SELECTCOLUMNS(
        FILTER(ALL(B), B[year] <= CurrentYear),
        "GrowthFactor", 1 + (B[index %] / 100)
    )
// Calculate the cumulative product of all growth factors
VAR CumulativeInflationFactor = PRODUCTX(InflationGrowthFactors, [GrowthFactor])
// Apply the factor to the current row's cash flow
RETURN A[cashflow] * CumulativeInflationFactor

Breakdown of the Formula #

1. CurrentYear Variable: Stores the year from the current row in Table A to make our filter logic cleaner.
2. InflationGrowthFactors Variable:
   • ALL(B) clears any default filters from the relationship between Table A and Table B, so we can access all years in Table B.
   • FILTER keeps only rows where Table B's year is less than or equal to the current row's year.
   • SELECTCOLUMNS converts the percentage inflation value into a growth multiplier (e.g., 3% inflation becomes 1.03).
3. CumulativeInflationFactor Variable: Uses PRODUCTX to iterate over our growth factors and calculate their cumulative product—this is more reliable than PRODUCT for row-by-row calculations in a column context.
4. Final Calculation: Multiplies the original cash flow by the cumulative inflation factor to get the indexed value.

Important Notes #

• Double-check that the year columns in both tables have the same data type (e.g., both are integers representing the year, not date values).
• If your index % in Table B is already stored as a decimal (e.g., 0.02 instead of 2), remove the / 100 part from the formula.
• Ensure there are no missing index % values for years in Table B—empty values will cause the product calculation to return blank.

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