PowerBI分析技巧：如何根据起止日期进行数据统计？

最近遇到几个类似的问题，根据项目/合同的起止日期来统计，某时间点或者区间的相关指标，比如下面这个项目数据，

这是我们经常会碰到的数据结构，包含有两列日期，开始日期和结束日期，常见的需求是，如何按月查看尚未完成的项目有多少，以及对应的项目金额？

下面就来看看如何利用PowerBI来实现这个需求，步骤如下：

1，建立数据模型

因为要按日期来计算，所以建立一个独立的日期维度表是必要的，关于建立日期表的方法见：玩PowerBI必备的日期表制作方式汇总

项目表有两列日期，那么应该用哪个日期与日期表建立关系呢？对于本文的需求来说，其实可以不建立任何关系，模型如下：

2，创建度量值

统计每月末尚未完成的项目数量，其逻辑很简单，就是筛选开始日期早于月末，而结束日期晚于月末的项目，度量值可以这么写：

期末未完成项目数量 = VAR maxdate_=MAX('日期表'[日期])VAR activeitems=FILTER( '项目表', '项目表'[开始日期]<=maxdate_ &&'项目表'[结束日期]>maxdate_)RETURN COUNTROWS( activeitems )

‍

其实这个度量值不仅仅可以统计每月末的未完成项目数量，还能统计任何一个区间，比如每天、每季度末、每年末的数量。

筛选出符合条件的项目以后，统计未完成项目的金额只需要在此基础上求和汇总就行了：

期末未完成项目金额 = VAR maxdate_=MAX('日期表'[日期])VAR activeitems=FILTER( '项目表', '项目表'[开始日期]<=maxdate_&& '项目表'[结束日期]>maxdate_)RETURN CALCULATE( SUM('项目表'[项目金额]) , activeitems )

3、统计指标可视化

可以用组合图展示上面计算的两个指标：

这就是按月查看未完成项目的数量和金额指标。

如果需要计算每期处理的项目数量和项目金额，应该怎么做呢？这个指标是只要项目经过本期，无论是否到期，都应该统计进去，不仅仅是期末尚未到期的，还包括在本期内到期的项目。

可以在上述度量值的基础上加上本期内到期的项目数据，也可以计算出来，不过显得太繁琐了，这里给出一个更简洁的写法：

本期处理项目数量 = VAR mindate_=MIN('日期表'[日期])VAR maxdate_=MAX('日期表'[日期])VAR processitems=FILTER( '项目表', MAX('项目表'[开始日期],mindate_)<=MIN('项目表'[结束日期],maxdate_))RETURN COUNTROWS( processitems )

这个逻辑充分利用了本期上下文的第一天和最后一天，以及项目的开始日期、结束日期，将在本期出现过的项目的逻辑，形成这一行表达式：

MAX('项目表'[开始日期],mindate_)<=MIN('项目表'[结束日期],maxdate_)

如果你不是太明白，可以根据一个项目的实际起止日期和上下文的最大最小日期来推演，更容易理解这个逻辑。

同理，计算本期处理的项目金额度量值如下：

本期处理项目金额 = VAR mindate_=MIN('日期表'[日期])VAR maxdate_=MAX('日期表'[日期])VAR processitems=FILTER( '项目表', MAX('项目表'[开始日期],mindate_)<=MIN('项目表'[结束日期],maxdate_))RETURN CALCULATE( SUM('项目表'[项目金额]), processitems )

以上就是解决此类问题的通用模式，理解了这几个度量值的计算逻辑以后，这一类问题都可以迎刃而解。