Power BI Dax计算3个周期的平均值

Question

我在Dim_Date中有两个下面的表，周期不对应于确切的月份，首先我计算了一个度量，将每个区域的记录计数除以NumofWeeks。

一周的序号在同一时间段内总是相同的。

Table1

<表类> 地区 次区域 DataID 数量 tbody><<tr>北中部密苏里12042022123000北中部明尼苏达州12052022170000北达科他州北达科他州10042022234000东北纽约08042022500000东北新泽西12052022578000内华达州12032022679000亚利桑那州10032022654000

我接受这个方案，非常感谢…

[M 3-Period Rolling Avg] =
// Set the number of periods to calculate
// the average over.
var PeriodCount = 3
// First, get the last visible period.
var LastPeriodSeqno = MAX( Dim_Date[PeriodSeqno] )
// Then, get the periods over which to calc.
var PeriodsToAverageOver =
CALCULATETABLE(
DISTINCT( Dim_Date[PeriodSeqno] ),
// Here's the place where you use the fact that
// all the periods are consecutively numbered.
// In fact, the counting does not have to start
// at 1 but it has to increment by 1.
Dim_Date[PeriodSeqno] <= LastPeriodSeqno,
Dim_Date[PeriodSeqno] > LastPeriodSeqno - PeriodCount,
REMOVEFILTERS( Dim_Date )
)
// We need to make sure that there are indeed
// PeriodCount periods in the set. Otherwise,
// the average will not be correct. This could happen
// if we were too close to the beginning of the calendar.
var ShouldCalculate =
COUNTROWS( PeriodsToAverageOver ) = PeriodCount
var Result =
if( ShouldCalculate,
CALCULATE(
AVERAGEX(
PeriodsToAverageOver,
[M]
),
REMOVEFILTERS( Dim_Date )
)
)
return
Result

我的日历不同例如时期就像金融日历周期

• 2021_04 -开始于205/04/2021，结束于202/05/2021
• 2021_05 - 2021年5月3日开始，2021年5月30日结束
• 2021_06 -开始于2021年5月31日，结束于2021年7月4日

我要计算

Divide per region week =
VAR TotalCountPerRegion = COUNT(Table1[Region])
VAR tblNumOfWeeksInPeriod=
SUMMARIZE(
DimDate
,DimDate[Period]
,DimDate[NumofWeeks]
)
VAR SuMOfWeeksInPeriod = SUMX(tblNumOfWeeksInPeriod,DimDate[NumofWeeks])
RETURN
MROUND(
DIVIDE(
TotalCountPerRegion
,SuMOfWeeksInPeriod
)
,1
)

如果我们从2021_04开始滚动3个周期，我有

• 2021_06 =平均值(2021_06+2021_05+2021_04)
• 2021_07 =平均值(2021_07+2021_06+2021_05)
• 2021_08 =平均(2021_08+2021_07+2021_06)等等

我需要合并上面的公式，因为平均值是公式中三个周期的总和。

Thanks in advance