Power BI DAX实现连续3个期间平均值计算的技术问询

Alright, let's tackle this moving average requirement. Since your Period values don't map to actual calendar months, we can't use standard date offset logic—instead, we'll base the calculation on the sort order of your Period values directly. Here's a step-by-step DAX solution:

DAX Measure Code #

3-Period Moving Average = 
VAR CurrentPeriod = SELECTEDVALUE(DimDate[Period])
-- Get all unique Periods in the current filter context
VAR AllPeriods = ALLSELECTED(DimDate[Period])
-- Assign a continuous rank to each Period (adjust sort logic if needed)
VAR PeriodRanks = 
    ADDCOLUMNS(
        AllPeriods,
        "@Rank", RANKX(AllPeriods, [Period],, ASC, DENSE)
    )
-- Get the rank of the current Period
VAR CurrentRank = MAXX(FILTER(PeriodRanks, [Period] = CurrentPeriod), [@Rank])
-- Filter to include current Period + next two consecutive Periods
VAR TargetPeriods = 
    SELECTCOLUMNS(
        FILTER(PeriodRanks, [@Rank] >= CurrentRank && [@Rank] <= CurrentRank + 2),
        "@Period", [Period]
    )
-- Calculate average of the week-over-region values for the target Periods
RETURN
    AVERAGEX(
        TargetPeriods,
        [Divide per region week]
    )

How This Works #

Let's break down each part to make sure it fits your use case:

1. CurrentPeriod: Grabs the specific Period value active in the current report context (e.g., 2021_04 when that row is selected).
2. AllPeriods: Captures all unique Periods available under your current filters (uses ALLSELECTED to respect any external slicers/filters you have applied).
3. PeriodRanks: Assigns a dense, continuous rank to each Period. The ASC sort uses the text order of your Periods (e.g., 2021_04 → 1, 2021_05 → 2, etc.). If your Periods need a different sort order (like tied to actual dates), replace [Period] in the RANKX with the relevant date column from your DimDate table.
4. CurrentRank: Finds the rank number for the current Period.
5. TargetPeriods: Filters the ranked list to include only the current Period and the next two consecutive Periods (based on their rank).
6. Final Calculation: Uses AVERAGEX to iterate over the target Periods, pull the Divide per region week value for each, and compute the average. This automatically handles edge cases (e.g., the last two Periods will only average the available values instead of throwing errors).

Example Verification #

For your sample data:

• For 2021_04, the target Periods are 2021_04, 2021_05, 2021_06. The average becomes (17 + 15 + 9)/3 = 13.67 (add MROUND(..., 1) around the result if you need whole numbers, matching your original measure's rounding).
• For 2021_05, the target Periods are 2021_05, 2021_06, 2021_07. The average becomes (15 + 9 + 16)/3 = 13.33.

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