数据透视表：动态计算近N天数据变化

在Excel中，我们可以使用Power Pivot和数据透视表相结合的方法来动态计算近N天的数据变化的情况。比如，我们按选择一个日期，计算当前日期的前7天、前15天，前30天等近期的数据变化情况。如图所示：

这种方法不仅可以提高数据透视表的效率，还可以打造更多的分析的维度。

初始的数据源和数据模型如下图所示：

在这个模型中，我们新建一个日期表，用来筛选订单表中的下单日期。这个例子是简化过来的。

为了当我们选择一个日期的时候，在我们透视表中和数据透视图中能显示选择的近N天的数据，我们还需要做两件事：

（1）新建一个用于切片器的近N天的表。如图所示。可以在excel工作表中输入，然后导入到Power Pivot中。

（2）按日期表再建立一个用于透视图的x轴和透视表的日期列的日期表。同时该表也标记为日期表。

以上这两个表，不与原有的模型建立任何的关系。如图所示：

紧接着，我们开始书写度量值。

首先我们需要一个简单的用于计算总金额的度量值，即：

总金额:= SUM('订单表'[金额])

其次我们需要一个计算近N天的总金额的度量值salestotal。即：

salestotal:=VAR maxdte =    IF ( HASONEVALUE ( '日期表'[Date] ),     VALUES ( '日期表'[Date] ) )//获取切片器中筛选的日期的值VAR t =    IF ( HASONEVALUE ( '近N天'[近N天] ),     VALUES ( '近N天'[近N天值] ) )//获取切片器中选择的近N天的值VAR sv =    CALCULATE (        [总金额],        DATESBETWEEN ( '日期表'[Date], maxdte - t + 1, maxdte ),        //计算切片器中所选的日期的近N天的日期范围        INTERSECT ( ALL ( '日期表'[Date] ), VALUES ( '切片日期表'[Date] ) )    )//建立虚拟的连接关系,相当于Treatas函数的作用RETURN    sv

在Excel的Power Pivot中（除365的预算版本）中没有SELECTEVALUE函数和TREATA函数，所以：

SELECTEDVALUE函数我们可以使用IF+HASONEVALUE+VALUES函数的方法来替代，也可以使用MAX或者MIN等相关的函数来替换。TREATAS函数可以使用INTERSECT+ALL+AVLUES函数组合的方法来替代。

建立拟关系的作用是因为表中我们使用的是物理关系的日期表来做切片器来控制多个透视表或者透视图，所以需要一个虚拟关系来搭接日期表与切片日期表，用于筛选。

插入一个用于选择日期的切片器，日期来自于日期表中的日期列。

插入一个用于选择近N天的切片器，切片器的值来自于近N天表中近N天列。

插入一个数据透视表，日期列来自于切片日期表中的日期列，放入度量值salestotal。如图所示，当我们选择一个日期的时候，就可以自动计算这个日期的近N天的总金额。

接着插入一个数据透视图，图表类型修改为拆白线图，x轴的日期列为切片日期表中的日期列，度量值为salestotal。如图所示：

当我们要计算选定日期和近N天各个产品的saletotal时，我们就可们再插入一个透视表，行标签放入产品名称，值放入度量值salestotal。如图所示：

到这里还远远没有结束，因为你透视表和透视图还不能真正地联动起来，此时，我们可以对两个切片器进行设置，选择切片器，设置报表连接。如图所示：

全部勾选连接到数据透视表和数据透视图，这样就能正常地工作了。

但是还有一个问题就是图表的标题要随着选择的近N天的值变化，可以结合度量值，CUBE类函数以及文本框和公式的方法来解决。这里大家可以自行尝试。

当然对于使用Power BI和Excel 365预览版的朋友们，由于有SELECTEDVALUE函数和TREATAS函数的存在 ，所以度量值就可以简单地写为：

salestotal-N = VAR sv =    SELECTEDVALUE ( '日期表'[Date] )VAR t =    SELECTEDVALUE ( '近N天'[值] )VAR sv2 =        CALCULATE (            SUM ( '订单表'[金额] ),            TREATAS ( VALUES ( '切片日期表'[Date] ), '日期表'[Date] ),            DATESBETWEEN('日期表'[Date],sv-(t-1),sv)        )RETURN    sv2

在Power BI中的效果如图所示：

关于近N天的案例，我们还可以拓展到年/月的近N个月/近N年的例子，我们都可以在这个基础上进行改造得到。关注后期的内容，给大家具体地讲解。

让我看看，是哪个小可爱翻到惊喜了~~

小必老师近期新出版的《Excel商务智能：Power Query和Power Pivot数据清洗、建模与分析实战》一书，在上市以来就取得不俗的表现，豆瓣上更是有了9.5分的评分。本书有50+的实战案例，有1G+的随书重难点章节的配套视频。

京东限时五折优惠，快快扫码抢购吧！

每日抽奖赠书

发布：刘恩惠
审核：陈歆懿 

如果喜欢本文欢迎 在看丨留言丨分享至朋友圈 三连<  PAST · 往期回顾  >‍‍
《数字中国建设整体布局规划》发布，技术人如何趁势而起？

点击阅读原文，查看本书详情！