近日,阿迪达斯发布了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章内容。