【DAX 系列】PowerBI 期初期末的数据结构与过程计算模式

接上回，我们探讨了期初期末的DAX计算方法。

本文对此进一步深入探讨。

半累加度量

在完整理解期初期末的计算前，我们要明白一个概念，叫半累加度量。

通俗来讲，可以被累加数学运算的数字字段就是可度量字段，例如：销售额，利润，成本。本文字段除了计数不可以累加，叫做不可累加字段，如：地点，姓名，手机号等。

而半累加度量是介于可累加度量与不可累加度量之间的一种业务字段，如：利润率，年龄，年份序号，期初余额，期末余额它虽然不可以累加，但却可以取得初值或末值以及最大最小值。

不难发现，这种半累加度量都表示某种状态，而非过程，例如：年份序号，年龄，期初期末就是一种状态，而不表示中间的过程。

我们在上次给出的方案就是适合这种类型的。

但还有一种类型就是在管理过程中，保留了过程值，但却需要计算最终的状态。

存货表结构

存货表的结构如下：

这个结构保存在存货过程中的年初盘点，新增，销售三个过程量。它的特点在于如果要计算某个日期的期初或期末要考虑到 YTD 的整个过程。

我们对三种状态做透视表，可以看出：

一般我们在年初，尤其是1月1日，录入整个年份的初始记录，接着随着时间的发展，某日期的期末的计算逻辑如下：

某日期的期末 = 年初初始化 + YTD新增（补货） - YTD销售（出库）

构造基础度量值

基础度量值如下：

Data.KPI.状态.期初库存 = CALCULATE( [Data.KPI.Core] , Data[阶段] = "初始化存货" )

Data.KPI.状态.本期销售 = CALCULATE( [Data.KPI.Core] , Data[阶段] = "本期销售" )

Data.KPI.状态.本期新增 = CALCULATE( [Data.KPI.Core] , Data[阶段] = "本期新增" )

可以看出，这个定义就是一种对过程的计算。

接着，我们要获得状态的值。

年初余额

首先，是年初期初余额，如下：

Data.KPI.年度期初 =
CALCULATE( [Data.KPI.状态.期初库存] , STARTOFYEAR( Model_Calendar[Date] ) )

期末余额

有了年初余额，期末余额是年初余额与整个YTD过程的和，如下：

Data.KPI.期末 =
[Data.KPI.年度期初]
+
CALCULATE( [Data.KPI.状态.本期新增] - [Data.KPI.状态.本期销售] , DATESYTD( Model_Calendar[Date] ) )

这里使用到的过程量就是之前所定义的。

期初余额

有了期末余额，我们可以用一种技巧来求期初余额，如下：

Data.KPI.期初 =
IF(
    FIRSTDATE( Model_Calendar[Date] ) = DATE( SELECTEDVALUE( Model_Calendar[年份序号] ) , 1 , 1 ) ,
    [Data.KPI.年度期初] ,
    CALCULATE( [Data.KPI.期末] , PREVIOUSDAY( FIRSTDATE( Model_Calendar[Date] ) ) )
)

请注意这里的技巧：

PREVIOUSDAY( FIRSTDATE( Model_Calendar[Date] ) )

这里使用了日期智能函数的连续运算模式，非常方便。

最终效果

这样，我们就从过程量计算得到了状态量，这个过程如下：

最后，我们可以得到在任何一个级别都是计算正确的期初期末，我们放大如下：

不难看出，我们在任何一个级别的计算都是正确的。

总结

我们用了两种方式来给出了期初期末的计算和对应的数据结构。

• 状态型
  • 数据结构的数据表示状态，用获取模式。 如： 获取期初期末。
  • 不同层级的计算使用层级方法。
  • 高级技巧： ISINSCOPE 与 层级状态码
• 过程型
  • 数据结构的数据表示过程，用累加模式。 如： 期末 = 年度期初 + YTD过程。
  • 不同层级的计算使用累加方法。
  • 高级技巧： 期初 = 上期期末 与日期智能的连用。

至此，期初期末就彻底解决了。

至此，从时间角度计算的所有情况在历史的文章中已经全部涵盖。

它们包括：日期表，（绝对）日期区间表，（相对）日期区间表，上期同期（年，季，月，周，日），XTD（YTD，QTD，MTD，WTD），上期全部（去年全年，上季度，上月，上周，昨天），过去X天（移动平均，滚动日期），期初期末（状态式，过程式），移动平均，AC（实际），PY（上期），FC（预测），PL（目标），财年，运营月，运营周，以及上述内容的排列组合。