如何用Power BI进行工作日相关的计算?
我们平时应该都会碰到针对工作日的分析,在Power BI中,并没有与工作日相关的函数,很多人因此就无从下手,不知道该如何计算了,那么,如何用PowerBI处理工作日的分析呢?这篇文章给你提供个思路。
考虑到非工作日,不仅有周末,还有法定节假日,并且涉及到调休等问题,很难有某个固定的函数,进行这些不规则的个性化计算,即使Excel中有工作日函数,也并不方便,直接使用的计算结果也很可能与实际不符。
其实最简单的办法就是先做一个节假日的列表,你可以在网上找每年节假日的资料,即使是手工制作,也不麻烦,毕竟周末是很规律的,法定节假日和调休的处理每年也就是十几天而已,很快就可以做出来这张表。
这里我先做出一个2019年到2021年的中国节假日表:
然后将这个假日表导入到PowerBI数据模型中,这里不将它与模型中的其他表建立关系,而是利用它,在模型原有的日期表中,添加关于工作日、节假日的字段。
首先判断每个日期是否为工作日,添加一列工作日标记:
工作日标记 = IF( LOOKUPVALUE( '中国节假日表'[日期], '中国节假日表'[日期], [日期] ), 0 , 1 )
同样的方式,利用LOOKUPVALUE,将每个日期对应的节假日名称和节假日类别匹配过来,如果不是节假日,就返回空白:
节假日名称 =
LOOKUPVALUE(
'中国节假日表'[节假日名称],
'中国节假日表'[日期],
[日期]
)
节假日类型 =
LOOKUPVALUE(
'中国节假日表'[节假日类型],
'中国节假日表'[日期],
[日期]
)
在日期表中显示如下(节选):
为了能够灵活进行工作日的计算,这里我再添加一个字段工作日编号,为每个工作日添加一个不重复的顺序索引。
工作日编号 =
IF(
[工作日标记]=1,
CALCULATE(
SUM('日期表'[工作日标记]),
FILTER(ALL('日期表'),[日期]<=EARLIER('日期表'[日期]))
)
)
这个工作日编号非常好用,下面会看到它的用处。
至此,完成对日期表的补充构造,下面就可以开始工作日的相关计算了。
统计某期间的工作日天数
计算两个日期之间的工作日的天数,只需要这样写一个度量值:
工作日天数 = SUM('日期表'[工作日标记])
是不是非常简单,这就是工作日标记用0和1的好处。
上/下个工作日
如果想找出某个日期的上一个工作日,就可以这样写:
上个工作日 =
CALCULATE(
MAX('日期表'[日期]),
FILTER(
ALL('日期表'),
'日期表'[日期]<MAX('日期表'[日期])&&
'日期表'[工作日标记]=1
)
)
这个度量值的逻辑就是找小于当前日期,并且工作日标记为1的最大日期,也就是上一个工作日。
同理,下一个工作日的写法:
下个工作日 =
CALCULATE(
MIN('日期表'[日期]),
FILTER(
ALL('日期表'),
'日期表'[日期]>MAX('日期表'[日期])&&
'日期表'[工作日标记]=1
)
)
效果如下:
N个工作日后是哪一天
日常经常会遇到某些事项的办结期限是10个工作日、15个工作日等,那么如何快速计算出N个工作日后,是哪一天呢?
这样的计算需要先建立一个参数,然后写个度量值:
这里就用到了日期表中添加的工作日编号,通过这个编号的加减,就可以快速定位到N个工作日后是哪一天。
这里的参数我也设置了负数,如果是-10,实际上是10个工作日前是哪一天。
以上就是关于工作日的几种常见计算,有了日期,再计算这些日期对应的业务数据就很简单了。
上面的计算并没有专门的函数,我们只需要掌握工作日的逻辑常识,配合常用的DAX函数就可以实现各种工作日相关的分析需要,其中的关键是,先完善带有假日标记的日期表。
这种做法,和之前介绍的关于周的分析,思路是一致的:学会了这个思路,你也可以轻松进行周分析!
当然本文构造的日期表中不仅有工作日,还有各种节假日字段,进行假日的分析同样很便捷,比如想比较每一年春节对应的销售数据,只需要一个切片器就可以了。