177_模型_Power BI 进销存6大日期维度期初与期末

一、背景

在经销存报表设计中，经常会遇到的便是期初与期末。当然我们这里说期初与期末指的是期初库存与期末库存。

这里的期一般常见的会有：年月日。本案例将演示 6 大日期维度，分别是：日、周、月、季度、半年度、年度。

先来看一下示例结果(**注意：**这里我们只是将6个日期维度进销存展示出来，里面还有很多变形需要结合实际业务去展开)。

Power BI 公共 web 效果：https://demo.jiaopengzi.com/pbi/177-full.html

图-01

二、模型与DAX

这里的示例模型都是我们常用的模型(https://jiaopengzi.com/1435.html)

表间关系如下：

图-02

主要度量值

01_入库

01_入库 = 
SUM ( 'T03_入库信息表'[F_02_入库产品数量] )

02_出库

02_出库 = 
CALCULATE (
    SUM ( 'T05_订单子表'[F_05_产品销售数量] ),
    USERELATIONSHIP ( 'T04_订单主表'[F_04_送货日期], A00_Calendar[C01_Dates] )
)

入库和出库都是相对简单的聚合即可，需要注意的是，出库我们使用的送货日期，这里需要使用USERELATIONSHIP激活关系。

03_库存_期末

库存其实很好理解，就是累计的入库减去累计的出库即可。

这里需要注意：期初库存，期末库存都是入库与出库通过计算衍生出来的，只要把入库和出库在各个时间节点上关系捋清楚了就很好理解了。

当然这里的入库和出库是泛指的，比如盘亏，盘盈，这些都要与之对应。

图-03

03_库存_期末 = 
VAR DATE_START0 =
    CALCULATE ( FIRSTDATE ( 'A00_Calendar'[C01_Dates] ), ALL ( 'A00_Calendar' ) )
VAR DATE_END0 =
    LASTDATE ( 'A00_Calendar'[C01_Dates] )
VAR DATE_TABLE0 =
    DATESBETWEEN ( 'A00_Calendar'[C01_Dates], DATE_START0, DATE_END0 )
VAR IN0 =
    CALCULATE ( [01_入库], DATE_TABLE0 )
VAR OUT0 =
    CALCULATE ( [02_出库], DATE_TABLE0 )
VAR DATE_START_ABS0 =
    CALCULATE ( LASTDATE ( 'T04_订单主表'[F_04_送货日期] ), ALL () ) //兼容显示到事实表最后日期。
RETURN
    IN0 - OUT0
//IF(DATE_START_ABS0>=DATE_END0,IN0 - OUT0,BLANK())

04_库存_期初

期初是在期末的基础上对各个日期维度做了兼容所以会看起来很复杂，其实相对比较简单，只要找到各个时间点的前一个粒度的期末即为期初。

如果只需要一个时间维度的话，可以将下面度量值简化。

04_库存_期初 = 
VAR DATE_START0 =
    CALCULATE ( FIRSTDATE ( 'A00_Calendar'[C01_Dates] ), ALL ( 'A00_Calendar' ) )
VAR DATE_END0 =
    LASTDATE ( 'A00_Calendar'[C01_Dates] )
VAR TF0 =
    HASONEVALUE ( A00_Calendar[C01_Dates] ) //日
VAR TF1 =
    HASONEVALUE ( 'A00_Calendar'[C12_YearWeek] ) //周
VAR TF2 =
    HASONEVALUE ( 'A00_Calendar'[C20_YearMonth] ) //月
VAR TF3 =
    HASONEVALUE ( 'A00_Calendar'[C24_YearQuarter] ) //季度
VAR TF4 =
    HASONEVALUE ( 'A00_Calendar'[C27_YearHalf] ) //半年度
VAR TF5 =
    HASONEVALUE ( 'A00_Calendar'[C29_FY00] ) //年度
VAR N0 =
    SWITCH (
        TRUE (),
        TF0, 0,
        TF1,
            DATEDIFF (
                FIRSTDATE ( 'A00_Calendar'[C31_StartOfWeek] ),
                LASTDATE ( 'A00_Calendar'[C36_EndOfWeek] ),
                DAY
            ),
        TF2,
            DATEDIFF (
                FIRSTDATE ( 'A00_Calendar'[C32_StartOfMonth] ),
                LASTDATE ( 'A00_Calendar'[C37_EndOfMonth] ),
                DAY
            ),
        TF3,
            DATEDIFF (
                FIRSTDATE ( 'A00_Calendar'[C33_StartOfQuarter] ),
                LASTDATE ( 'A00_Calendar'[C38_EndOfQuarter] ),
                DAY
            ),
        TF4,
            DATEDIFF (
                FIRSTDATE ( 'A00_Calendar'[C34_StartOfHalfYear] ),
                LASTDATE ( 'A00_Calendar'[C39_EndOfHalfYear] ),
                DAY
            ),
        TF5,
            DATEDIFF (
                FIRSTDATE ( 'A00_Calendar'[C35_StartOfYear] ),
                LASTDATE ( 'A00_Calendar'[C40_EndOfYear] ),
                DAY
            ),
        0
    ) + 1
VAR DATE_END1 =
    SWITCH (
        TRUE (),
        TF0, DATEADD ( DATE_END0, - N0, DAY ),
        TF1, DATEADD ( LASTDATE ( 'A00_Calendar'[C36_EndOfWeek] ), - N0, DAY ),
        TF2, DATEADD ( LASTDATE ( 'A00_Calendar'[C37_EndOfMonth] ), - N0, DAY ),
        TF3, DATEADD ( LASTDATE ( 'A00_Calendar'[C38_EndOfQuarter] ), - N0, DAY ),
        TF4, DATEADD ( LASTDATE ( 'A00_Calendar'[C39_EndOfHalfYear]), - N0, DAY ),
        TF5, DATEADD ( LASTDATE ( 'A00_Calendar'[C40_EndOfYear] ), - N0, DAY ),
        DATE_START0 //无筛选的时，默认日期表期初。
    )
VAR DATE_END2 =
    IF ( ISBLANK ( DATE_END1 ), DATE_START0, DATE_END1 ) //兼容dateadd后的空值，注意日期表的两个端点。
VAR DATE_TABLE0 =
    DATESBETWEEN ( A00_Calendar[C01_Dates], DATE_START0, DATE_END2 )
VAR IN0 =
    CALCULATE ( [01_入库], DATE_TABLE0 )
VAR OUT0 =
    CALCULATE ( [02_出库], DATE_TABLE0 )
VAR DATE_START_ABS0 =
    CALCULATE ( LASTDATE ( 'T04_订单主表'[F_04_送货日期] ), ALL () ) //兼容显示到事实表最后日期。
RETURN
    IN0 - OUT0
//IF ( DATE_START_ABS0 >= DATE_END2, IN0 - OUT0, BLANK () )

三、总结

这里的期初期末都需要和日期表配合，特别是期初，需要和我们调制的日期表( https://jiaopengzi.com/2916.html )配合。
要捋清楚，入库出库都是实在发生的(包括盘亏盘盈)，期初库存，期末库存还有安全库存( https://jiaopengzi.com/2753.html )这些指标都是通过实际入库出库衍生计算出来的。