动态计算店铺数量

发布于

近日，阿迪达斯发布了2020年财报，其中一张报表是店铺数量，如下图所示：

该报表显示了阿迪达斯每个季度的店铺数量，以及到2020年底的店铺数量（注意年底数量=Q4数量），另外，还显示了每个季度的开店数量，关店数量，净开/关店数量。

如何在Excel/Power BI中仅凭一个完整店铺资料制作类似以上报表（如下透视表所示）？下文以Excel界面进行讲述，Power BI软件操作相同。

1.数据准备

准备好店铺资料表，有店铺ID、店铺名称、开业日期、关闭日期等字段。

准备一个日期表，仅有日期列即可，日期完整覆盖店铺资料表的所有开业日期、关闭日期年份（本例为2017-2021年）。

将这两个表格导入Power Pivot：

导入后如下界面所示：

2.计算

在Power Pivot后台，选择日期表，添加必要的年、季度、月计算列：

代码语言：javascript
复制
年 = YEAR('日期表'[日期])
月 = MONTH('日期表'[日期])
季度 ="Q"&FORMAT('日期表'[日期],"Q")

添加度量值，计算期末现存店铺数量、期间开店数量、期间关店数量、净开关店数量：

代码语言：javascript
复制
期末店铺数量:=CALCULATE (
    DISTINCTCOUNT ('店铺明细'[店铺ID]),
FILTER(
FILTER ( '店铺明细', '店铺明细'[开业日期] <= MAX ( '日期表'[日期] )),
'店铺明细'[关闭日期]> MAX ( '日期表'[日期] )
            || '店铺明细'[关闭日期]= BLANK ()
    )
)
代码语言：javascript
复制
期间开店数量 :=
CALCULATE (
    DISTINCTCOUNT ( '店铺明细'[店铺ID] ),
FILTER (
'店铺明细',
'店铺明细'[开业日期] <= MAX ( '日期表'[日期] )
            && '店铺明细'[开业日期] >= MIN ( '日期表'[日期] )
    )
)
代码语言：javascript
复制
期间关店数量 :=
CALCULATE (
    DISTINCTCOUNT ( '店铺明细'[店铺ID] ),
FILTER (
'店铺明细',
'店铺明细'[关闭日期] <= MAX ( '日期表'[日期] )
            && '店铺明细'[关闭日期] >= MIN ( '日期表'[日期] )
    )
)
代码语言：javascript
复制
净开关店数量:=[期间开店数量]-[期间关店数量]

公式设置完成后，拉个透视表/矩阵呈现结果。例如上海市2019年底店铺数量204家，2020年第一季度新开13家，关闭3家，净开10家，总店铺数量第一季度结束时达到214家。

在本例中，日期表和店铺明细无需建立关系，使用时间智能函数时等操作时，日期表需要与数据中的日期建立关系。但本例日期表只是用来筛选。当日期表与店铺明细中的开店日期、关店日期中间的任意一列建立关系时，会导致错误的计算值。

类似的，HR领域计算在职员工、离职员工、新员工是同样的套路，可参考拙作《Power BI商业数据分析项目实战》第17章内容。

#bi#excel#pivot