2022-03-30 20:35
来源:
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 )
以上就是解决此类问题的通用模式，理解了这几个度量值的计算逻辑以后，这一类问题都可以迎刃而解。
Excel轻松管理文档，这个Power Query技巧你会用吗？
Power BI帕累托分析：数值相等时的计算错误问题
PowerBI可视化技巧，利用DAX轻松自定义时间轴
采悟，微软MVP,「PowerBI星球」创始人，《Power BI商业数据分析》作者返回搜狐，查看更多
责任编辑：}